提供最多书籍的供应商 | 提供最多书籍的供应商

本文介绍了提供最多书籍的供应商的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

create table test1(bid varchar(10),bname varchar(75),suid varchar(10))
insert into test1 values('B01','Java How To Do Program','S01')
insert into test1 values('B011','c','S03')
insert into test1 values('B02','Java: The Complete Reference ','S03')
insert into test1 values('B03','Java How To Do Program','S01')
insert into test1 values('B04','Java: The Complete Reference ','S01')
insert into test1 values('B05','Java How To Do Program','S01')
insert into test1 values('B06','Java: The Complete Reference ','S03')
insert into test1 values('B07','Let Us C','S03')
insert into test1 values('B08','Let Us C','S04')
insert into test1 values('B09','oops','S03')

create table test2(suid varchar(25),sname varchar(25))
insert into test2 values('S01','A')
insert into test2 values('S02','B')
insert into test2 values('S03','C')
insert into test2 values('S04','D')
insert into test2 values('S05','E')
insert into test2 values('S06','F')

注意::不要提sid = ??这样..

i需要这样输出

NOTE:: Dont mention sid=?? this way..

i need output like this

bid bname sname
B011 c C
B02 Java: The Complete Reference C
B06 Java: The Complete Reference C
B07 Let Us C C
B09 oops C

推荐答案

SELECT [bid],[bname],[sname]
  FROM [test1] INNER JOIN [test2] ON [test1].[suid] = [test2].[suid]
  WHERE [sname] = ''C''

DECLARE @MaxBookCount INT
SELECT @MaxBookCount= (SELECT TOP 1 COUNT(bid) FROM test1 GROUP BY suid ORDER BY COUNT(bid) DESC)

SELECT B.bid, B.bname, S.sname
FROM Test1 B
INNER JOIN Test2 S ON S.suid=B.suid
WHERE B.suid IN (SELECT suid,
                 FROM Test1
                 GROUP BY suid
                 HAVING COUNT(B.bid)=@MaxBookCount)

问候，

GVPrabu

Regards,
GVPrabu

这篇关于提供最多书籍的供应商的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！