表一:pro_Table 产品表
字段:pid(产品id),pName(产品名称),pUserID(对应会员表id)表二:User_Table 会员表
字段:uid(会员id),uName(会员名称),uFlag(int,会员级别)表三:Card_Table 竞价表。
字段:cid(竞价id),price(decimal,竞价价格),cUserID(对应会员id)其中:pUserID=uid,cUserID=uid.这是一个会员发布产品的数据库表。产品显示顺序是根据竞价的大小倒序排列,并且,每个会员只显示最新的一条。
如结果:
pid pName uName price23 联想电脑 huiyuanA 2.50
36 打印机 hyx 2.3
109 显示器 asking 1.8
16 彩色打印机 sayfree 0.9
....................................
请教各位高手,这个语句怎么写。
字段:pid(产品id),pName(产品名称),pUserID(对应会员表id)表二:User_Table 会员表
字段:uid(会员id),uName(会员名称),uFlag(int,会员级别)表三:Card_Table 竞价表。
字段:cid(竞价id),price(decimal,竞价价格),cUserID(对应会员id)其中:pUserID=uid,cUserID=uid.这是一个会员发布产品的数据库表。产品显示顺序是根据竞价的大小倒序排列,并且,每个会员只显示最新的一条。
如结果:
pid pName uName price23 联想电脑 huiyuanA 2.50
36 打印机 hyx 2.3
109 显示器 asking 1.8
16 彩色打印机 sayfree 0.9
....................................
请教各位高手,这个语句怎么写。
(
select max(price) price,cUserID from Card_Table group by cUserID
) C
join
User_Table U
on C.cUserID=U.uid
join pro_Table P
on U.uid=P.pUserID
select
a.pid,
a.pname,
b.uname,
c.price
from
pro_Table a
left join
User_Table b on a.pUserID=b.uid
left join
Card_Table c
on c.cUserID=b.uid
and not exists(select 1 from Card_Table where cUserID=c.cUserID and cid>c.cid)
with ZS as(
select a.pid,a.pName,b.uName,b.price from pro_Table a,User_Table b,Card_Table c
where a.pUserID=b.uid and c.cUserID=b.uid)select row=row_number() over(partition by uName,order by price desc),a.pid,a.pName,b.uName,b.price
from ZS where row=1
SELECT AA.pid,AA.pName,BB.uName,CC.price FROM pro_Table AA INNER JOIN User_Table BB
ON AA.pUserId=BB.uid INNER JOIN
(SELECT * FROM Card_Table C WHERE NOT EXISTS(SELECT 1 FROM Card_Table WHERE cUserId=A.cUserId AND cid>C.cid))
CC ON BB.uid=CC.cUserId
ORDER BY CC.Price DESC
a.pid,a.pname,b.name,c.price
from
pro_Table ,User_Table b,Card_Table c
where
a.pUserID=b.uid
and
c.cUserID=b.uid
and
c.price=(select top 1 price from Card_Table where cid=c.cid oredr by price desc)
a.pid,a.pname,b.name,c.price
from
pro_Table ,User_Table b,Card_Table c
where
a.pUserID=b.uid
and
c.cUserID=b.uid
and
c.price=(select top 1 price from Card_Table where cUserID=c.cUserID oredr by price desc)
select a.pid,a.pName,b.uName,max(isnull(c.price,0))
from pro_Table a inner join User_Table b
on a.pUserID=b.uid left outer join Card_Table c
on a.pUserID=c.cUserID
group by a.pid,a.pName,b.uName
declare @User_Table table(uid varchar(36),uName varchar(40),uFlag int)
declare @Card_Table table(cid varchar(36),price decimal,cUserID varchar(36))select p.pid,p.pName,u.uName,c.price from (select cUserID,max(price) as 'price' from @Card_Table group by cUserID) c,@pro_Table p,@User_Table u
where p.pUserID=u.uid and u.uid=c.cUserID
select
a.pid,a.pname,b.name,c.price
from
pro_Table ,User_Table b,Card_Table c
where
a.pUserID=b.uid
and
c.cUserID=b.uid
and
c.price=(select top 1 price from Card_Table where cUserID=c.cUserID oredr by price desc)
from pro_Table a inner join User_Table b on a.pUserID=b.uid
inner join Card_Table c on b.uid=c.cUserID
group by a.pid,a.pName,b.uName
a.pid,
a.pname,
b.uname,
c.price
from
pro_Table a
left outer join
User_Table b
on
a.pUserID=b.uid
left outer join
Card_Table c
on
c.cUserID=b.uid
and
not exists
(
select 1 from Card_Table where cUserID=c.cUserID and cid>c.cid
)