table原始資料為
[date]       [field]
[2001/1/1 08:00:00][1a1]
[2001/1/1 12:00:00][1a2]
[2001/1/2 12:00:00][1a1]
[2001/1/2 13:00:00][1a2]
利用group by 所搜尋出來的語法及資料
SELECT * CONVERT(char(10), date, 111) AS date, COUNT(field) AS field
FROM table
WHERE (field <> '')
GROUP BY CONVERT(char(10), date, 111)
ORDER BY date
傳回結果
[date]  [field]
[2001/1/1][1]
[2001/1/2][1]
而若再加上field的統計
SELECT * CONVERT(char(10), date, 111) AS date, substring(field,2,2) as field, COUNT(field) AS cf
FROM table
WHERE (field <> '')
GROUP BY CONVERT(char(10), date, 111),substring(field,2,2)
ORDER BY date,field
傳回結果
[date]  [field] [cf]
[2001/1/1][a1]  [1]
[2001/1/1][a2]  [1]
[2001/1/2][a1]  [1]
[2001/1/2][a2]  [1]
以上皆無問題...不過我想利用交叉表..再針對field再做分組統計...以下是我下的指令....照理說應該是沒問題...不過執行時確無法剖析....是否因為substring or convert無法在transform裡做分析呢??煩請前輩賜教...謝謝....
======================
語法:
Transform count(field) as cf
select convert(char(10),date,111)
form table
where (field <> '')
group by convert(char(10),date,111)
order by date
pivot substring(fields,2,2)
希望傳回結果
[date]   [a1][a2]
[2001/1/1] [1] [1]
[2001/1/2] [1] [1]
=======================
arrow
arrow
    全站熱搜

    vbqa 發表在 痞客邦 留言(1) 人氣()