英文:
Shorthand for group by all columns?
问题
我有一个包含15列客户信息的表，我想将他们的购买总额与表格连接起来。我目前正在尝试以下连接：
SELECT A.*, SUM(B.SALES_AMT) AS SALES
FROM CUST_INFO A
LEFT JOIN SALES_TABLE B
ON A.Cust_ID = B.Cust_ID
GROUP BY fooa, foob, fooc, etc
是否有一种方法可以使用GROUP BY A.*而不必写出所有15列左右的列名？
英文:
I have a table with 15 columns with customer info and I want to join the sum of their purchases onto the table. I am currently attempting the following join:
SELECT A.*, SUM(B.SALES_AMT) AS SALES
FROM CUST_INFO A
LEFT JOIN SALES_TABLE B
ON A.Cust_ID = B.Cust_ID
GROUP BY fooa, foob, fooc, etc
Is there a way to group by where I can simply say GROUP BY A.* instead of having to write out all 15 or so columns?
答案1
得分: 3
你可以将求和部分移到 apply 中：
select A.*, Isnull(B.SALES, 0) SALES
from CUST_INFO A
outer apply (
select Sum(B.SALES_AMT) SALES
from SALES_TABLE B
where B.Cust_ID = A.Cust_ID
)B;
英文:
You could move the sum into an apply:
select A.*, Isnull(B.SALES, 0) SALES
from CUST_INFO A
outer apply (
select Sum(B.SALES_AMT) SALES
from SALES_TABLE B
where B.Cust_ID = A.Cust_ID
)B;
答案2
得分: 2
将您的销售数据分组在一个子查询中
SELECT A.*, B.SALES
FROM CUST_INFO A
LEFT JOIN (
SELECT B.Cust_ID, SUM(B.SALES_AMT) SALES
FROM SALES_TABLE B
GROUP BY B.Cust_ID
) B ON A.Cust_ID = B.Cust_ID;
英文:
Group your sales data in a sub-query
SELECT A.*, B.SALES
FROM CUST_INFO A
LEFT JOIN (
SELECT B.Cust_ID, SUM(B.SALES_AMT) SALES
FROM SALES_TABLE B
GROUP BY B.Cust_ID
) B ON A.Cust_ID = B.Cust_ID;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论