运营常用SQL查询语句

按日期字段(天) 分组聚合统计SQL语句

SELECT CONVERT(nvarchar, a) + '-' + CONVERT(nvarchar, b) + '-' + CONVERT(nvarchar, c) 
      AS [date], data
FROM (SELECT YEAR(crdate) AS a, MONTH(crdate) AS b, DAY(crdate) AS c, SUM(Field) 
              AS data
        FROM Table
        GROUP BY YEAR(crdate), MONTH(crdate), DAY(crdate)) aa
ORDER BY [date]

关联更新

UPDATE tHLSuoHaBeiBiGoodsTemp
SET itemprice =
          (SELECT itemprice
         FROM tCHDIteminfo
         WHERE itemid = tHLSuoHaBeiBiGoodsTemp.itemid)

UPDATE tLuckyBoxPos
SET state = 1
WHERE (id IN
          (SELECT TOP 800 id
         FROM tLuckyBoxPos))

UPDATE t1
SET trophyid =
          (SELECT TOP 1 trophyid
         FROM t2
         WHERE t2.trophyname = t1.trophyname)