SQL: select top 3 * from food where price>=100 and price<=200 order by NEWID() MySQL: select * from food where price>=100 and price<=200 order by RAND() LIMIT 0 , 3
select top 3 * from food a where (select sum(price) from food b where b.foodid=a.foodid) between 100 and 200 order by NEWID()
谢谢你的回答,但是我用的是mysql数据库 于是应该执行为 select * from food a where (select sum(price) from food b where b.foodid=a.foodid) between 100 and 200 order by rand() limit 3 报1064错误。
不好意思,说错了, mysql里面只能这样执行 select * from food a where (select sum(price) from food b where b.foodid=a.foodid) between 100 and 200 order by rand() limit 3 但是里面执行结果是只有一条数据,我希望是3条。
终于明白是啥意思了:在N多数据里,取出3个,这3个的总价在100到200,这样的“组合”就是所求,随机显示出这样的其中一个“组合”。这可以写个存储过程做个循环,比较好理解。如写一句SQL,我只想到了一个3倍笛卡尔积的查询,不过会N慢吧... SQL:create table food(foodid int ,foodname varchar(50),price money) go declare @i int set @i=1 WHILE @i<200 begin insert into food select @i,'foodname'+CONVERT(varchar(3),@i),@i*3 set @i=@i+1 end go with t as ( select t1.foodid foodid1,t1.foodname foodname1,t1.price price1 ,t2.foodid foodid2,t2.foodname foodname2,t2.price price2 ,t3.foodid foodid3,t3.foodname foodname3,t3.price price3 ,ROW_NUMBER() over(order by NEWID()) r from food t1,food t2,food t3 where ((t1.price+t2.price+t3.price) between 100 and 200) and (t1.foodid<>t2.foodid and t1.foodid<>t3.foodid and t2.foodid<>t3.foodid) ) select foodid1 foodid,foodname1 foodname ,price1 price from t where t.r=1 union all select foodid2 foodid,foodname2 foodname ,price2 price from t where t.r=1 union all select foodid3 foodid,foodname3 foodname ,price3 price from t where t.r=1 go drop table foodMySQL:自己转化吧,不会了。
求高手指教啊!
什么时候CSDN也这么冷清了。
MySQL: select * from food where price>=100 and price<=200 order by RAND() LIMIT 0 , 3
感谢你的回答,但是好像price只是单价,我希望得到3个price的总和是在100-200范围内的mysql语句
where (select sum(price) from food b where b.foodid=a.foodid) between 100 and 200
order by NEWID()
于是应该执行为
select * from food a
where (select sum(price) from food b
where b.foodid=a.foodid) between 100 and 200
order by rand() limit 3
报1064错误。
不好意思,说错了,
mysql里面只能这样执行
select * from food a
where (select sum(price) from food b
where b.foodid=a.foodid) between 100 and 200
order by rand() limit 3
但是里面执行结果是只有一条数据,我希望是3条。
结果一测~~才发现是错的~~~我又一次平静了~~~
SQL:create table food(foodid int ,foodname varchar(50),price money)
go
declare @i int
set @i=1
WHILE @i<200
begin
insert into food select @i,'foodname'+CONVERT(varchar(3),@i),@i*3
set @i=@i+1
end
go
with t as (
select t1.foodid foodid1,t1.foodname foodname1,t1.price price1
,t2.foodid foodid2,t2.foodname foodname2,t2.price price2
,t3.foodid foodid3,t3.foodname foodname3,t3.price price3
,ROW_NUMBER() over(order by NEWID()) r
from food t1,food t2,food t3
where ((t1.price+t2.price+t3.price) between 100 and 200) and (t1.foodid<>t2.foodid and t1.foodid<>t3.foodid and t2.foodid<>t3.foodid)
)
select foodid1 foodid,foodname1 foodname ,price1 price from t where t.r=1
union all
select foodid2 foodid,foodname2 foodname ,price2 price from t where t.r=1
union all
select foodid3 foodid,foodname3 foodname ,price3 price from t where t.r=1
go
drop table foodMySQL:自己转化吧,不会了。
不过非常感谢你提供了这个方法,我慢慢转化试试看能不能搞定。