查询门店客户流失到其他门店的排行。如同城市有10家门店，想知道哪家店的客户去别的门店消费的最多

[恶魔的幻影]

版本 SQLserver 2012
需求：查询店客户到其他门店的消费客户，门店的排行，及合计客户数，
如同一城市有10家门店，想知道哪家店的客户去别的门店消费的最多

请各位高手帮忙！

现在有字段

ID   车牌号     消费门店编号，消费门店名称，消费时间
id，che_no，GongSiNo，GongSiMc，xche_jsrq
1   津A12345     01      天津1店        2018-06-07 16:16
2   津A12345     01      天津1店        2018-06-10 16:16
3   津A45678     06      天津6店        2018-05-06 16:16
4   津A12345     06      天津6店        2018-05-02 16:16
5   津A12345     01      天津1店        2018-04-04 16:16

现在想到的SQL语句，只能查询出来去过其他店的车牌，但是实现不了需求
with t as (
select che_no,GongSiMc from work_pz_sj 
where
-- che_no='黑E67H17'
xche_jsrq>'2016-01-01 00:00' and xche_jsrq<'2018-06-07 23:59'
and GongSiNo in('02','08')
group by che_no,GongSiMc having count(che_no) >=2
 )
 select che_no,MIN(GongSiMc) from t group by che_no having count(che_no) >=2

[二月十六]

测试数据对应的结果是什么？

[恶魔的幻影]

引用 1 楼 sinat_28984567 的回复:

测试数据对应的结果是什么？

您是想问我想要的结果吗 还是我那个语句筛选的结果

[二月十六]

引用 2 楼 aqiang566971 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:] 测试数据对应的结果是什么？

您是想问我想要的结果吗 还是我那个语句筛选的结果[/quote] 基于测试数据，想要的正确结果。 这样帮你写完，大家也都知道写的对不对 了

[RINK_1]

你这设计的有些不合理，应该有个表记录每个车牌的指定消费门店。现在只能把每个车牌的第一次消费门店看做是它的指定消费门店了。

 with cte
 as
 (select *,ROW_NUMBER() over (partition by che_no order by xche_jsrq) as seq from 消费明细表)
 
 select GongSiNo,GongSiMc,COUNT(1) as total from cte A
 where exists (select 1 from cte where A.che_no=che_no and seq=1 and A.GongSiNo<>GongSiNo)
 group by GongSiNo,GongSiMc

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[che_no] nvarchar(27),[GongSiNo] nvarchar(22),[GongSiMc] nvarchar(24),[xche_jsrq] DATETIME)
Insert #T
select 1,N'津A12345',N'01',N'天津1店','2018-06-07 16:16' union all
select 2,N'津A12345',N'01',N'天津1店','2018-06-10 16:16' union all
select 3,N'津A45678',N'06',N'天津6店','2018-05-06 16:16' union all
select 4,N'津A12345',N'06',N'天津6店','2018-05-02 16:16' union all
select 5,N'津A12345',N'01',N'天津1店','2018-04-04 16:16'
Go
--测试数据结束
;WITH CTE AS (
SELECT che_no,
       GongSiNo,
       [GongSiMc],
       xche_jsrq,
       ROW_NUMBER() OVER (PARTITION BY che_no ORDER BY xche_jsrq) rn
FROM #T
)
SELECT CTE.GongSiNo,
       [GongSiMc],
       (
           SELECT COUNT(1)
           FROM #T
           WHERE che_no = CTE.che_no
                 AND xche_jsrq > CTE.xche_jsrq
                 AND GongSiNo <> CTE.GongSiNo
       ) AS 合计
FROM CTE
WHERE rn = 1;

[恶魔的幻影]

引用 3 楼 sinat_28984567 的回复:

[quote=引用 2 楼 aqiang566971 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 测试数据对应的结果是什么？

您是想问我想要的结果吗 还是我那个语句筛选的结果[/quote] 基于测试数据，想要的正确结果。 这样帮你写完，大家也都知道写的对不对 了[/quote] 想要的结果是 现在有字段 消费明细表 ID 车牌号 消费门店编号，消费门店名称，消费时间 id，che_no，GongSiNo，GongSiMc，xche_jsrq 1 津A12345 01 天津1店 2018-06-07 16:16 2 津A12345 01 天津1店 2018-06-10 16:16 3 津A45678 06 天津6店 2018-05-06 16:16 4 津A12345 06 天津6店 2018-05-02 16:16 5 津A12345 01 天津1店 2018-04-04 16:16 希望得到的结果是 天津1店 有1台车去其他门店消费过 天津6店 有0台车去其他门店消费过 门店编号，消费门店名称， GongSiNo，GongSiMc 合计 01 天津1店 1 06 天津6店 0

[恶魔的幻影]

现在是查出合计了，但是我没有办法验证数据是否正确，能否列出合计数里面的车牌明细呢。

[恶魔的幻影]

现在是查出合计了，但是我没有办法验证数据是否正确，能否列出合计数里面的车牌明细呢。 比如 车牌 消费时间 门店名称 津A12345 2018-06-07 16:16 天津1店 津A12345 2018-06-15 10:18 天津2店