select CONVERT(varchar(10),b.createDate,120) as createDate, a.id,a.goods_name,a.goods_pku,pp.catname,s.site_name,
sc.cname,a.goods_url,gpi.goods_img_small,gpi.goods_img_big,
b.goods_price_change,b.goods_price
from goods_products a
left join goods_price_history_2011_09 b on a.id=b.goods_id
left join goods_pp pp on pp.id=a.goods_ppid
left join site s on a.siteid=s.id
left join site_category sc on sc.id=a.goods_site_catid
left join goods_products_info gpi on gpi.goodsid=a.id
group by a.id,a.goods_name,a.goods_pku,pp.catname,s.site_name,sc.cname,a.goods_url,gpi.goods_img_small,gpi.goods_img_big, createDate,b.goods_price_change,b.goods_price
having createDate >='2011-09-01' and createDate<='2011-09-21' and a.id=96944 and b.goods_price_change >0 order by id desc
go
这条语句的结果:
create id goods_name goods_pku catname site_name cname
1 2011-09-13 96944 索尼彩电KDL-60NX810 彩电 苏宁易购 3D电视
2 2011-09-15 96944 索尼彩电KDL-60NX810 彩电 苏宁易购 3D电视
goods_price_change goods_price
5000 34499
5000 34499
如何取出create为2011-09-15的一条数据
sc.cname,a.goods_url,gpi.goods_img_small,gpi.goods_img_big,
b.goods_price_change,b.goods_price
from goods_products a
left join goods_price_history_2011_09 b on a.id=b.goods_id
left join goods_pp pp on pp.id=a.goods_ppid
left join site s on a.siteid=s.id
left join site_category sc on sc.id=a.goods_site_catid
left join goods_products_info gpi on gpi.goodsid=a.id
group by a.id,a.goods_name,a.goods_pku,pp.catname,s.site_name,sc.cname,a.goods_url,gpi.goods_img_small,gpi.goods_img_big, createDate,b.goods_price_change,b.goods_price
having createDate >='2011-09-01' and createDate<='2011-09-21' and a.id=96944 and b.goods_price_change >0 order by id desc
go
这条语句的结果:
create id goods_name goods_pku catname site_name cname
1 2011-09-13 96944 索尼彩电KDL-60NX810 彩电 苏宁易购 3D电视
2 2011-09-15 96944 索尼彩电KDL-60NX810 彩电 苏宁易购 3D电视
goods_price_change goods_price
5000 34499
5000 34499
如何取出create为2011-09-15的一条数据
解决方案 »
- 注册服务器问题
- 求SQL语句???
- 如何改变存储过程的所有者
- 高分求教如何得到一个分组统计的数据,内容见帖,在线等待
- 数据库怎么收缩都小不下来! 急!请各位帮忙看看!
- SQL语言区分大小写么,大小写混合会出错么?
- 用SQL语句生成报表内容,已实现但是比较繁琐,求简洁思路
- 多表查询统计 送分
- ★★★各位大虾,怎样把游标由最后一行提到第一行,用FETCH first怎么不行,提示我“fetch: The fetch type first cannot be used with f
- 如何调整oracle8共享池大小
- 多条件SQL语句改写成一句(1个表数据)
- 请问SQL语句的条件(condition)子句前的加号的作用,谢谢指教。
sc.cname,a.goods_url,gpi.goods_img_small,gpi.goods_img_big,
b.goods_price_change,b.goods_price
from goods_products a
left join goods_price_history_2011_09 b on a.id=b.goods_id
left join goods_pp pp on pp.id=a.goods_ppid
left join site s on a.siteid=s.id
left join site_category sc on sc.id=a.goods_site_catid
left join goods_products_info gpi on gpi.goodsid=a.id
group by a.id,a.goods_name,a.goods_pku,pp.catname,s.site_name,sc.cname,a.goods_url,gpi.goods_img_small,gpi.goods_img_big, createDate,b.goods_price_change,b.goods_price
having createDate >='2011-09-01' and createDate<='2011-09-21' and a.id=96944 and b.goods_price_change >0 order by createDate desc
go
WHERE NOT EXISTS(SELECT 1 FROM #T WHERE id=T.id AND TIME>T.TIME)
select CONVERT(varchar(10),b.createDate,120) as createDate, a.id,a.goods_name,a.goods_pku,pp.catname,s.site_name,
sc.cname,a.goods_url,gpi.goods_img_small,gpi.goods_img_big,
b.goods_price_change,b.goods_price
from goods_products a
left join goods_price_history_2011_09 b on a.id=b.goods_id
left join goods_pp pp on pp.id=a.goods_ppid
left join site s on a.siteid=s.id
left join site_category sc on sc.id=a.goods_site_catid
left join goods_products_info gpi on gpi.goodsid=a.id
where CONVERT(varchar(10),b.createDate,120) ='2011-09-15' and a.id=96944 and b.goods_price_change >0
group by a.id,a.goods_name,a.goods_pku,pp.catname,s.site_name,sc.cname,a.goods_url,gpi.goods_img_small,gpi.goods_img_big, createDate,b.goods_price_change,b.goods_price
order by id desc
go
你group 干嘛了?你的查询里出没用聚合啊!
--having 是用于聚合函数取值条件的,你没用聚合函数,最好不要用having.
select CONVERT(varchar(10),b.createDate,120) as createDate, a.id,a.goods_name,a.goods_pku,pp.catname,s.site_name,
sc.cname,a.goods_url,gpi.goods_img_small,gpi.goods_img_big,b.goods_price_change,b.goods_price
from goods_products a
left join goods_price_history_2011_09 b on a.id=b.goods_id
left join goods_pp pp on pp.id=a.goods_ppid
left join site s on a.siteid=s.id
left join site_category sc on sc.id=a.goods_site_catid
left join goods_products_info gpi on gpi.goodsid=a.id
where CONVERT(varchar(10),b.createDate,120) ='2011-09-15' and a.id=96944 and b.goods_price_change >0
gorder by id desc
go
--要我回答,就他的查询语句丢到cte里,然后:
select * from cte a where not exists(
select 1 from cte where id=a.id and create>a.create
)
http://www.cnblogs.com/insus/articles/1935853.html