有9張表，分別是a至i，這9張表的欄位分別是日期和表名，查詢出來的結果是行數，有另一張匯總了所有表名的z表，現在要的結果是查詢出欄位為日期，表名，行數的集合，例如我是這樣寫的 select a.date , z.tablename, count from a,b,c,d,e,f,g,h,i,z where a.date = to_timestamp('20170910','yyyymmdd') and z.tablename in (select tablename from tmp_m_tablename); 這樣的話要怎么改才能正確？
uj5u.com熱心網友回復：
看的不是很懂
uj5u.com熱心網友回復：
同樣沒看太明白, 不過你寫的SQL中COUNT需要改成COUNT(*)uj5u.com熱心網友回復：
你這樣寫應該是會報錯group by 的問題；建議你這么寫(把九張表Count結果Union起來（剩下幾張表的Union陳述句復制一下就好）
SELECT date,tablename,COUNT(*) FROM a WHERE date = to_timestamp('20170910','yyyymmdd') GROUP BY date,table name UNION ALL
SELECT date,tablename,COUNT(*) FROM B WHERE date = to_timestamp('20170910','yyyymmdd') GROUP BY date,table name UNION ALL
uj5u.com熱心網友回復：
按照你的寫法，最后的結果回被放大，因為類似于CROSS JOIN 了，而且也沒有和Z 表進行關聯，只是把Z 表中的所有行查詢出來，同樣是CROSS JOIN.我覺得你可以參考上面 chenghangstar的寫法，或者這樣寫
SELECT 日期, 'a' AS 表名 , COUNT(*) AS 行數 FROM a WHERE 日期 = CAST('20180101' AS DATE)GROUP BY 日期
UNION ALL
SELECT 日期, 'b' AS 表名 , COUNT(*) AS 行數 FROM b WHERE 日期 = CAST('20180101' AS DATE)GROUP BY 日期
UNION ALL
SELECT 日期, 'c' AS 表名 , COUNT(*) AS 行數 FROM c WHERE 日期 = CAST('20180101' AS DATE)GROUP BY 日期
UNION ALL
SELECT 日期, 'd' AS 表名 , COUNT(*) AS 行數 FROM d WHERE 日期 = CAST('20180101' AS DATE)GROUP BY 日期
UNION ALL
SELECT 日期, 'e' AS 表名 , COUNT(*) AS 行數 FROM e WHERE 日期 = CAST('20180101' AS DATE)GROUP BY 日期
UNION ALL
SELECT 日期, 'f' AS 表名 , COUNT(*) AS 行數 FROM f WHERE 日期 = CAST('20180101' AS DATE)GROUP BY 日期
UNION ALL
SELECT 日期, 'g' AS 表名 , COUNT(*) AS 行數 FROM g WHERE 日期 = CAST('20180101' AS DATE)GROUP BY 日期
UNION ALL
SELECT 日期, 'h' AS 表名 , COUNT(*) AS 行數 FROM h WHERE 日期 = CAST('20180101' AS DATE)GROUP BY 日期
UNION ALL
SELECT 日期, 'i' AS 表名 , COUNT(*) AS 行數 FROM h WHERE 日期 = CAST('20180101' AS DATE)GROUP BY 日期
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/80866.html
標籤：數據倉庫