27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT
(SELECT c_short FROM xt_company c WHERE c.c_comcode=c_com) AS 'shop', --显示出各个店铺
(SELECT COUNT(c_clientcode) FROM kh_client WHERE kh_client.c_clientcode=ls_retail.c_retailcode)--显示客户表里的新会员
FROM ls_retail --零售表
WHERE c_date BETWEEN '2011-1-17 00:00:00' AND '2011-1-23 23:59:59'
GROUP BY c_com, c_retailcode
在你查出来的效果上
select a.店名,count(1) as 新店数量 from (你查出的) a group by a.店名
select c.c_short as shop,count(b.c_clientcode)code
from ls_retail a left join kh_client b on b.c_clientcode = a.c_retailcode
left join xt_company c on c.c_comcode = a.c_com
where a.c_date between '2011-1-17 00:00:00' and '2011-1-23 23:59:59'
group by c.c_short
select c.c_short as shop,count(*)code
from ls_retail a left join kh_client b on b.c_clientcode = a.c_retailcode
left join xt_company c on c.c_comcode = a.c_com
where a.c_date between '2011-1-17 00:00:00' and '2011-1-23 23:59:59'
group by c.c_short
--???
--带点数据吧!感觉这段SQL太繁琐了。
;WITH xt_company AS
(
SELECT c_short = N'天然店', c_comcode = 1 UNION ALL
SELECT N'经三路店', 2
),
kh_client AS
(
SELECT c_clientcode = 1, [name] = N'张三' UNION ALL
SELECT 2, N'李四'
),
ls_retail AS
(
SELECT c_com = 1, c_retailcode = 1, c_date = '2011-1-17 00:00:00' UNION ALL
SELECT 1, 2, '2011-1-23 23:59:59' UNION ALL
SELECT 2, 2, '2011-1-23 23:58:58'
)
--#1. 按照你的思路,外面加个GROUP BY即可
/*
SELECT shop, cnt = SUM(cnt) FROM
(
SELECT
(SELECT c_short FROM xt_company c WHERE c.c_comcode=c_com) AS 'shop', --显示出各个店铺
cnt = (SELECT COUNT(c_clientcode) FROM kh_client WHERE kh_client.c_clientcode=ls_retail.c_retailcode)--显示客户表里的新会员
FROM ls_retail --零售表
WHERE c_date BETWEEN '2011-1-17 00:00:00' AND '2011-1-23 23:59:59'
) a
GROUP BY shop
*/
--#2(李四参加了两个店哦)
SELECT 店名 = c_short, 新会员数量 = COUNT(*) FROM
(
SELECT c_com, c_short, c_clientcode, [name] FROM ls_retail a
INNER JOIN kh_client b
ON a.c_retailcode = b.c_clientcode AND a.c_date BETWEEN '2011-1-17 00:00:00' AND '2011-1-23 23:59:59'
LEFT JOIN xt_company c
ON a.c_com = c.c_comcode
) t
GROUP BY c_short