34,587
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT PRIMARY KEY,
dept NVARCHAR(10),
bfr NVARCHAR(10),
d DATETIME,
con NVARCHAR(20)
)
INSERT INTO t
SELECT 1 ,'华山派', '郭靖', '2018/6/20' ,'翻阅武当秘笈'
UNION ALL SELECT 2 ,'华山派', '郭靖', '2018/6/20' ,'少林经典'
UNION ALL SELECT 3 ,'华山派', '郭靖', '2018/6/21' ,'易筋经'
UNION ALL SELECT 4 ,'华山派', '郭靖', '2018/6/22' ,'葵花宝典'
UNION ALL SELECT 5 ,'华山派', '郭靖', '2018/6/22' ,'少林经典'
UNION ALL SELECT 6 ,'华山派', '郭靖', '2018/6/24' ,'抄写武当秘笈'
UNION ALL SELECT 7 ,'华山派', '郭靖', '2018/6/25' ,'少林经典'
UNION ALL SELECT 8 ,'华山派', '黄蓉', '2018/6/20' ,'参悟易筋经'
UNION ALL SELECT 9 ,'华山派', '黄蓉', '2018/6/22' ,'偷看少林经典'
UNION ALL SELECT 10 ,'华山派', '黄蓉', '2018/6/22' ,'进入藏经阁,抄写经卷'
UNION ALL SELECT 11 ,'华山派', '黄蓉', '2018/6/24' ,'查阅武当秘笈'
UNION ALL SELECT 12 ,'华山派', '黄蓉', '2018/6/25' ,'葵花宝典'
UNION ALL SELECT 13 ,'华山派', '蒋东国', '2018/6/20' ,'易筋经'
UNION ALL SELECT 14 ,'华山派', '蒋东国', '2018/6/21' ,'少林经典'
UNION ALL SELECT 15 ,'华山派', '蒋东国', '2018/6/20' ,'易筋经'
UNION ALL SELECT 16 ,'华山派', '蒋东国', '2018/6/20' ,'易筋经'
UNION ALL SELECT 17 ,'华山派', '蒋东国', '2018/6/22' ,'少林经典'
UNION ALL SELECT 18 ,'华山派', '蒋东国', '2018/6/24' ,'易筋经'
UNION ALL SELECT 19 ,'华山派', '蒋东国', '2018/6/25' ,'抄写少林经典'
UNION ALL SELECT 20 ,'华山派', '李开泰', '2018/6/20' ,'武当秘笈'
UNION ALL SELECT 21 ,'华山派', '李开泰', '2018/6/21' ,'偷看少林经典'
UNION ALL SELECT 22 ,'华山派', '李开泰', '2018/6/22' ,'武当秘笈'
UNION ALL SELECT 23 ,'华山派', '李开泰', '2018/6/23' ,'背诵武当秘笈'
UNION ALL SELECT 24 ,'华山派', '李开泰', '2018/6/24' ,'少林经典'
UNION ALL SELECT 25 ,'华山派', '李开泰', '2018/6/25' ,'葵花宝典'
GO
SELECT dept
, bfr AS [拜访人]
, Count(bfr) AS [拜访总次数]
, (SELECT COUNT(1) FROM (SELECT DISTINCT d FROM t AS b WHERE b.bfr=a.bfr) AS tt) AS [拜访总天数]
FROM t AS a
GROUP BY dept, bfr;
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT PRIMARY KEY,
dept NVARCHAR(10),
bfr NVARCHAR(10),
d DATETIME,
con NVARCHAR(20)
)
INSERT INTO t
SELECT 1 ,'华山派', '郭靖', '2018/6/20' ,'翻阅武当秘笈'
UNION ALL SELECT 2 ,'华山派', '郭靖', '2018/6/20' ,'少林经典'
UNION ALL SELECT 3 ,'华山派', '郭靖', '2018/6/21' ,'易筋经'
UNION ALL SELECT 4 ,'华山派', '郭靖', '2018/6/22' ,'葵花宝典'
UNION ALL SELECT 5 ,'华山派', '郭靖', '2018/6/22' ,'少林经典'
UNION ALL SELECT 6 ,'华山派', '郭靖', '2018/6/24' ,'抄写武当秘笈'
UNION ALL SELECT 7 ,'华山派', '郭靖', '2018/6/25' ,'少林经典'
UNION ALL SELECT 8 ,'华山派', '黄蓉', '2018/6/20' ,'参悟易筋经'
UNION ALL SELECT 9 ,'华山派', '黄蓉', '2018/6/22' ,'偷看少林经典'
UNION ALL SELECT 10 ,'华山派', '黄蓉', '2018/6/22' ,'进入藏经阁,抄写经卷'
UNION ALL SELECT 11 ,'华山派', '黄蓉', '2018/6/24' ,'查阅武当秘笈'
UNION ALL SELECT 12 ,'华山派', '黄蓉', '2018/6/25' ,'葵花宝典'
UNION ALL SELECT 13 ,'华山派', '蒋东国', '2018/6/20' ,'易筋经'
UNION ALL SELECT 14 ,'华山派', '蒋东国', '2018/6/21' ,'少林经典'
UNION ALL SELECT 15 ,'华山派', '蒋东国', '2018/6/20' ,'易筋经'
UNION ALL SELECT 16 ,'华山派', '蒋东国', '2018/6/20' ,'易筋经'
UNION ALL SELECT 17 ,'华山派', '蒋东国', '2018/6/22' ,'少林经典'
UNION ALL SELECT 18 ,'华山派', '蒋东国', '2018/6/24' ,'易筋经'
UNION ALL SELECT 19 ,'华山派', '蒋东国', '2018/6/25' ,'抄写少林经典'
UNION ALL SELECT 20 ,'华山派', '李开泰', '2018/6/20' ,'武当秘笈'
UNION ALL SELECT 21 ,'华山派', '李开泰', '2018/6/21' ,'偷看少林经典'
UNION ALL SELECT 22 ,'华山派', '李开泰', '2018/6/22' ,'武当秘笈'
UNION ALL SELECT 23 ,'华山派', '李开泰', '2018/6/23' ,'背诵武当秘笈'
UNION ALL SELECT 24 ,'华山派', '李开泰', '2018/6/24' ,'少林经典'
UNION ALL SELECT 25 ,'华山派', '李开泰', '2018/6/25' ,'葵花宝典'
GO
SELECT dept
, bfr AS [拜访人]
, Count(bfr) AS [拜访总次数]
, (SELECT COUNT(DISTINCT d) FROM t AS b WHERE b.bfr=a.bfr) AS [拜访总天数]
FROM t AS a
GROUP BY dept, bfr;