我正在連接以Snowflake查詢視圖表的行數資料Snowflake。我也在查詢與 View 表相關的元資料。我的查詢如下所示。我想知道我是否可以使用 UNION ALL 陳述句來迭代python？當我嘗試運行以下查詢時，我收到一條錯誤訊息，提示“view_table_3”不存在。
提前感謝您的時間和努力！
查詢獲取雪花視圖表的行數（帶有元資料）
view_tables=['view_table1','view_table2','view_table3','view_table4']
print(f""" SELECT * FROM (SELECT TABLE_SCHEMA,TABLE_NAME,CREATED,LAST_ALTERED FROM SCHEMA='INFORMATION_SCHEMA.VIEWS' WHERE TABLE_SCHEMA='MY_SCHEMA' AND TABLE_NAME IN ({','.join("'" x "'" for x in view_tables)})) t1
LEFT JOIN
(SELECT 'view_table1' table_name2, count(*) as view_row_count from MY_DB.SCHEMA.view_table1
UNION ALL SELECT {','.join("'" x "'" for x in view_tables[1:])},count(*) as view_row_count from MY_DB.SCHEMA.{','.join("" x "" for x.replace("'"," ") in view_tables)})t2
on t1.TABLE_NAME =t2.table_name2 """)
uj5u.com熱心網友回復：
如果您想動態地進行聯合，請將整個SELECT查詢放在生成器中，然后使用' UNION '.
sql = f'''SELECT * FROM INFORMATION_SCHEMA.VIEWS AS v
LEFT JOIN (
{' UNION '.join(f"SELECT '{table}' AS table_name2, COUNT(*) AS view_row_count FROM MY_SCHEMA.{table}" for table in view_tables)}
) AS t2 ON v.TABLE_NAME = t2.table_name2
WHERE v.TABLE_NAME IN ({','.join(f"'{table}'" for table in view_tables)})
'''
print(sql);
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/480953.html
上一篇：如何使用二進制陣列值迭代所有二維陣列-python？
下一篇：有沒有辦法在R中回圈復制值？