提供最多书籍的供应商 [英] Supplier who has supplied maximum number of books

问题描述

hi

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

推荐答案

正如Samresh所建议的，像这样的查询应该可以完成你的任务

As suggested by Samresh, a query like this should accomplish your task

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

您好，



查看此....

Hi,

Check this....

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

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