表Detail(Detail_id, Bill_no,Dish_id,... )Detail_id:int自增列,标识每条明细记录
Bill_no:消费概况的消费单号,一张消费单可有多条菜品记录(Dish_id)
Dish_id:标识每个不同的菜品比如Detail的记录如下:
Detail_id Bill_no Dish_id ...
1 001 101
2 001 102
3 001 108
4 002 101
5 002 103
6 002 108
7 003 101
8 003 108
9 004 101
10 004 108
11 004 109 我要查找与Dish_id=101关联度最大的菜品ID(就是查出Dish_id=108这条记录),应该怎么写?万分感谢!
Bill_no:消费概况的消费单号,一张消费单可有多条菜品记录(Dish_id)
Dish_id:标识每个不同的菜品比如Detail的记录如下:
Detail_id Bill_no Dish_id ...
1 001 101
2 001 102
3 001 108
4 002 101
5 002 103
6 002 108
7 003 101
8 003 108
9 004 101
10 004 108
11 004 109 我要查找与Dish_id=101关联度最大的菜品ID(就是查出Dish_id=108这条记录),应该怎么写?万分感谢!
with cte as
(
select Dish_id,count(*) as cnt
from tb
where Bill_no in (select Bill_no from tb where Dish_id = 101)
group by Dish_id
),cta as
(
select *
from cte
where cnt = (select max(cnt) from cte)
)select *
from tb
where Dish_id in (select Dish_id from cta)--乱写的!
我要在VS里面写sq语句的,可以一句写完吗?认真点,可以实现立马结贴给分
一张消费单Bill_no有可以几个菜品(Dish_id),我要找出一道菜品,他与Dish_id=101共同出现次数最多
from Detail a
where exists(select 1 from Detail b where a.Detail=b.Detail and b.Dish_id=108)
and Dish_id<>108
group by Dish_id order by count(Dish_id) desc
select top 1 Dish_id,count(*) from Detail where Dish_id<>'101'
and Bill_no in (select distinct Bill_no from Detail where Dish_id='101')
group by Dish_id
select top 1 Dish_id,count(Bill_no) from Detail where Dish_id<>'101'
and Bill_no in (select distinct Bill_no from Detail where Dish_id='101')
group by Dish_id
order by count(Bill_no) desc
insert into @T
select 1, '001', 101 union all
select 2, '001', 102 union all
select 3, '001', 108 union all
select 4, '002', 101 union all
select 5, '002', 103 union all
select 6, '002', 108 union all
select 7, '003', 101 union all
select 8, '003', 108 union all
select 9, '004', 101 union all
select 10, '004', 108 union all
select 11, '004', 109
select top 2 dish_id,count(1) as cT from
(select * from @T where bill_no in (select bill_no from @T where Dish_id=101)) A
group by dish_id order by count(1) descdish_id cT
101 4
108 4