论坛用户名：TravyLee高手请现身，帮忙看下这个SQL查询条件该怎么加
这个语句是上次在论坛里提问后修改的，得到的结果是：1、从表中每个对应用户都只提取出一条产品显示，优先提取用户最新发布或更新的那条产品，如果用户发布产品时间都是相同的，那么在这样情况下就提取它ID最大产品

sql="SELECT * FROM SMT_cp a INNER JOIN SMT_yp ON a.SMT_yp_id = SMT_yp.SMT_id WHERE (NOT EXISTS (SELECT 1 FROM SMT_cp AS b WHERE a.SMT_yp_id = b.SMT_yp_id AND (a.SMT_date < b.SMT_date OR (a.SMT_date = b.SMT_date AND a.SMT_id < b.SMT_id)))) AND(SMT_yp.SMT_key = 1) ORDER BY a.SMT_date DESC"


现在想要加上一个搜素条件(SMT.SMT_cpname like'%"&饮料&"%') ，不知道该加在什么地方才是正确的。


这是上次的帖子：http://topic.csdn.net/u/20120702/16/229204a2-d307-4e31-8dbf-f488f50277a6.html?seed=1212121675&r=79008881#r_79008881 #28楼 TravyLee大哥的回答是正确的，现在就是想根据#28楼答案加入根据用户的产品名称进行筛选。



[解决办法]

SQL code

--> 测试数据：[SMT_cp]if object_id('[SMT_cp]') is not null drop table [SMT_cp]create table [SMT_cp]([SMT_id] int,[SMT_yp_id] int,[SMT_data] datetime,[SMT_cpname] varchar(6))goinsert [SMT_cp]select 1,9,'2012-4-12 14:33:36','饮料' union allselect 2,11,'2012-4-18 19:23:36','食品' union allselect 3,9,'2012-4-12 14:33:36','饮料' union allselect 4,9,'2012-4-12 14:33:36','香水' union allselect 5,12,'2012-5-19 19:23:36','饮料' union allselect 6,11,'2012-3-17 19:23:36','饮料' union allselect 7,12,'2012-5-19 19:23:36','饮料' union allselect 8,8,'2012-5-20 19:23:36','香油' union allselect 9,8,'2012-5-17 19:23:36','饮料' union allselect 10,8,'2012-5-18 19:23:36','饮料' union allselect 11,8,'2012-5-16 19:23:36','饮料' union allselect 12,10,'2012-5-19 19:23:36','口服液' union allselect 13,10,'2012-5-19 19:23:36','豆腐' union allselect 14,13,'2012-5-19 19:23:36','保健品' union allselect 15,13,'2012-6-19 16:23:41','饮料' union allselect 16,14,'2012-6-19 16:23:41','饮料'go--> 测试数据：[SMT_yp]if object_id('[SMT_yp]') is not null drop table [SMT_yp]create table [SMT_yp]([SMT_id] int,[SMT_coname] varchar(4))goinsert [SMT_yp]select 8,'王明' union allselect 9,'陈丽' union allselect 10,'海风' union allselect 11,'秋雨' union allselect 12,'夏天' union allselect 13,'毛毛' union allselect 14,'小黄'go;with tas(select     a.*,    b.SMT_coname from     [SMT_cp] ainner join     [SMT_yp] bon     a.SMT_yp_id=b.SMT_idwhere    charindex('饮料',a.SMT_cpname)>0)select     *from    t awhere    not exists(        select             1        from             t b        where            a.SMT_yp_id=b.SMT_yp_id            and (                (a.SMT_data<b.SMT_data)                 or (a.SMT_data=b.SMT_data and a.SMT_id<b.SMT_id)                )     )/*SMT_id    SMT_yp_id    SMT_data    SMT_cpname    SMT_coname------------------------------10    8    2012-05-18 19:23:36.000    饮料    王明3    9    2012-04-12 14:33:36.000    饮料    陈丽6    11    2012-03-17 19:23:36.000    饮料    秋雨7    12    2012-05-19 19:23:36.000    饮料    夏天15    13    2012-06-19 16:23:41.000    饮料    毛毛16    14    2012-06-19 16:23:41.000    饮料    小黄*/
[解决办法]
如果你的数据库是sql2000，用临时表试试
SQL code
select a.*,b.SMT_coname into #tfrom [SMT_cp] ainner join [SMT_yp] bon  a.SMT_yp_id=b.SMT_idwhere charindex('饮料',a.SMT_cpname)>0goselect *from #t awhere not exists(  select 1 from #t b  where a.SMT_yp_id=b.SMT_yp_id  and ((a.SMT_data<b.SMT_data) or (a.SMT_data=b.SMT_data and a.SMT_id<b.SMT_id)))