SELECT top 10 A.A_ID, B.B_ID,C.C_ID FROM 表A
INNER JOIN 表B ON "条件1"
INNER JOIN 表C ON "条件2"
where "条件3" order by A.A_ID desc以上是我的查询语句,会出现重复的ID,请问需要怎么写才能读取不重复的ID呢?在线等,谢谢
INNER JOIN 表B ON "条件1"
INNER JOIN 表C ON "条件2"
where "条件3" order by A.A_ID desc以上是我的查询语句,会出现重复的ID,请问需要怎么写才能读取不重复的ID呢?在线等,谢谢
解决方案 »
- 函數自遞歸問題
- 程序查询时长时间没有反应,重启SQL服务后才能正常查询
- 请教问题 急!!!
- SQL2005版本
- 请大家帮我看看这个存储过程中的游标有什么问题,为什么不循环
- 存储过程的参数问题
- SQL Server Analysis Services (MSSQLSERVER)服务启动不了
- 一列中同记录的值只显示第一条,求SQL
- 按条件将一个表分为两个表?
- 请各位高手指点,游标问题。
- 本人用SQL2005做数据库,Delphi7.0做界面软件,数据库数据来源是从Excle中导入,请问在Delphi界面上还要设计”导入数据“这个按钮吗?
- 求助还原msserver2005问题 'G:\ximin\C#work\数据库\HR.mdf' 上的媒体簇的结构不正确。SQL Server 无法处理此媒体簇
AID BID CID
1 2 3
1 3 4
1 5 3那你选BID和CID的哪个?如果不选,不可能去重
要是只有id重复其他字段不重复,你是要只保留一条数据么?
直接加上distinct似乎不起作用哦
INNER JOIN 表B ON "条件1"
INNER JOIN 表C ON "条件2"
where "条件3"
group by B.B_ID,C.C_ID
order by A.A_ID desc
换句话说,正如2楼说的,就是要这条记录就OK
AID BID CID
1 2 3
from (select A.A_ID, B.B_ID,C.C_ID ,row_number()over(partition by A.A_ID order by A.A_ID desc ) id
FROM 表A
INNER JOIN 表B ON "条件1"
INNER JOIN 表C ON "条件2"
where "条件3" )
where id=1
order by A_ID desc
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070
order by RichPic.RichPic_ID desc9楼,帮忙改一下~~
from (
select RichPic.*, Users.FaceUrl,Users.NicName,Users.Users_ID, PlusCopy.*, Plus.* ,row_number(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc ) id
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070 )a
where id=1
order by a.RichPic_ID desc
你的图2、3那两条是完全一样?
--取得ID最大的一条记录
select * from tb t where not exists(select 1 from tb where col1=t.col1 and id>t.id)
SELECT top 4 *
from (
select RichPic.*, Users.FaceUrl,Users.NicName,Users.Users_ID, PlusCopy.*, Plus.* ,row_number(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc ) id
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070 )a
where id=1
order by a.RichPic_ID desc
帮忙检查一下代码?出错
from (
select RichPic.*, Users.FaceUrl,Users.NicName,Users.Users_ID, PlusCopy.*, Plus.* ,row_number()OVER(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc ) id
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070 )a
where id=1
order by a.RichPic_ID desc
不好意思,代码贴错了,一下红色部分是不是有误?请帮忙检查
SELECT top 4 *
from (
select RichPic.*, Users.FaceUrl,Users.NicName,Users.Users_ID, PlusCopy.*, Plus.* ,row_number(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc ) id
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070 )a
where id=1
order by a.RichPic_ID desc
看还能看明白一点~~~但是报错呀!~~具体报什么错呢你看他26楼row_number的写法就知道了
你看看版主的回复,你的row_number()写错了,row_number() 的正确写法是:row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc)也就是:SELECT top 4 *
from (
select RichPic.*, Users.FaceUrl,Users.NicName,Users.Users_ID, PlusCopy.*, Plus.* ,row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc ) id
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070 )a
where id=1
order by a.RichPic_ID desc
你看看版主的回复,你的row_number()写错了,row_number() 的正确写法是:row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc)也就是:SELECT top 4 *
from (
select RichPic.*, Users.FaceUrl,Users.NicName,Users.Users_ID, PlusCopy.*, Plus.* ,row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc ) id
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070 )a
where id=1
order by a.RichPic_ID desc
谢谢各位指点
还有一个问题:这样写会有一个问题,当不同表有相同的字段名就会提示出错,比如以上需要读取:RichPic.Title,Plus.Title....
而这些我都要读取哦~~
你看看版主的回复,你的row_number()写错了,row_number() 的正确写法是:row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc)也就是:SELECT top 4 *
from (
select RichPic.*, Users.FaceUrl,Users.NicName,Users.Users_ID, PlusCopy.*, Plus.* ,row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc ) id
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070 )a
where id=1
order by a.RichPic_ID desc
谢谢各位指点
还有一个问题:这样写会有一个问题,当不同表有相同的字段名就会提示出错,比如以上需要读取:RichPic.Title,Plus.Title....
而这些我都要读取哦~~把RichPic.*和Plus.* 改成具体的字段,比如:RichPic.Title as RichPic_Title,Plus.Title as Plus_Title
你看看版主的回复,你的row_number()写错了,row_number() 的正确写法是:row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc)也就是:SELECT top 4 *
from (
select RichPic.*, Users.FaceUrl,Users.NicName,Users.Users_ID, PlusCopy.*, Plus.* ,row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc ) id
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070 )a
where id=1
order by a.RichPic_ID desc
谢谢各位指点
还有一个问题:这样写会有一个问题,当不同表有相同的字段名就会提示出错,比如以上需要读取:RichPic.Title,Plus.Title....
而这些我都要读取哦~~把RichPic.*和Plus.* 改成具体的字段,比如:RichPic.Title as RichPic_Title,Plus.Title as Plus_Title是这样写吗?(我菜菜菜~~~~),还出错,麻烦完整滴写一下~~~
你看看版主的回复,你的row_number()写错了,row_number() 的正确写法是:row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc)也就是:SELECT top 4 *
from (
select RichPic.*, Users.FaceUrl,Users.NicName,Users.Users_ID, PlusCopy.*, Plus.* ,row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc ) id
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070 )a
where id=1
order by a.RichPic_ID desc
谢谢各位指点
还有一个问题:这样写会有一个问题,当不同表有相同的字段名就会提示出错,比如以上需要读取:RichPic.Title,Plus.Title....
而这些我都要读取哦~~把RichPic.*和Plus.* 改成具体的字段,比如:RichPic.Title as RichPic_Title,Plus.Title as Plus_Title是这样写吗?(我菜菜菜~~~~),还出错,麻烦完整滴写一下~~~
行,但你得把语句里表的字段都写出来,不要写成.*,呵呵,因为我不知道你的表里,有那些个字段
你看看版主的回复,你的row_number()写错了,row_number() 的正确写法是:row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc)也就是:SELECT top 4 *
from (
select RichPic.*, Users.FaceUrl,Users.NicName,Users.Users_ID, PlusCopy.*, Plus.* ,row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc ) id
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070 )a
where id=1
order by a.RichPic_ID desc
谢谢各位指点
还有一个问题:这样写会有一个问题,当不同表有相同的字段名就会提示出错,比如以上需要读取:RichPic.Title,Plus.Title....
而这些我都要读取哦~~把RichPic.*和Plus.* 改成具体的字段,比如:RichPic.Title as RichPic_Title,Plus.Title as Plus_Title是这样写吗?(我菜菜菜~~~~),还出错,麻烦完整滴写一下~~~
行,但你得把语句里表的字段都写出来,不要写成.*,呵呵,因为我不知道你的表里,有那些个字段我要读的字段:
表RichPic:
RichPic_ID,Users_ID,Title,Type,L_Url,M_Url,S_Url,Share_Url,L_Width,L_Height,Source,CreateTime,LastTime,BrowseTimes,CollectTimes,Recommended,SetTop,Re
表Users
Users_ID,NicName,FaceUrl
表PlusCopy:
Plus_ID,L_X,L_Y表Plus:
Plus_ID,Source_ID,Title,Description,URL,Price,L_Url,M_Url,S_Url,L_Width,L_Height,Code,CreateTime,LastTime,Type,QuoteTimes,CollectTimes,MouseOverTimes,ClickTimes,Recommended,Source,Classify就这些啦~~麻烦啦~~~
另外请指导一下我的网站http://www.tujiajia.cn
感觉自己没考虑数据的读取效率~~有机会请教一下各位
你看看版主的回复,你的row_number()写错了,row_number() 的正确写法是:row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc)也就是:SELECT top 4 *
from (
select RichPic.*, Users.FaceUrl,Users.NicName,Users.Users_ID, PlusCopy.*, Plus.* ,row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc ) id
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070 )a
where id=1
order by a.RichPic_ID desc
谢谢各位指点
还有一个问题:这样写会有一个问题,当不同表有相同的字段名就会提示出错,比如以上需要读取:RichPic.Title,Plus.Title....
而这些我都要读取哦~~把RichPic.*和Plus.* 改成具体的字段,比如:RichPic.Title as RichPic_Title,Plus.Title as Plus_Title是这样写吗?(我菜菜菜~~~~),还出错,麻烦完整滴写一下~~~
行,但你得把语句里表的字段都写出来,不要写成.*,呵呵,因为我不知道你的表里,有那些个字段我要读的字段:
表RichPic:
RichPic_ID,Users_ID,Title,Type,L_Url,M_Url,S_Url,Share_Url,L_Width,L_Height,Source,CreateTime,LastTime,BrowseTimes,CollectTimes,Recommended,SetTop,Re
表Users
Users_ID,NicName,FaceUrl
表PlusCopy:
Plus_ID,L_X,L_Y表Plus:
Plus_ID,Source_ID,Title,Description,URL,Price,L_Url,M_Url,S_Url,L_Width,L_Height,Code,CreateTime,LastTime,Type,QuoteTimes,CollectTimes,MouseOverTimes,ClickTimes,Recommended,Source,Classify就这些啦~~麻烦啦~~~
另外请指导一下我的网站http://www.tujiajia.cn
感觉自己没考虑数据的读取效率~~有机会请教一下各位你再试试,我把字段都重命名了,因为很多字段名,都是一样的:
SELECT top 4 *
from (
select
RichPic.RichPic_ID,
RichPic.Users_ID as R_Users_ID,
RichPic.Title as R_Title,
RichPic.Type as R_Type,
RichPic.L_Url as R_L_Url,
RichPic.M_Url as R_M_Url,
RichPic.S_Url as R_S_Url,
RichPic.Share_Url as R_Share_Url,
RichPic.L_Width as R_L_Width,
RichPic.L_Height as R_L_Height,
RichPic.Source as R_Source,
RichPic.CreateTime as R_CreateTime,
RichPic.LastTime as R_LastTime,
RichPic.BrowseTimes as R_BrowseTimes,
RichPic.CollectTimes as R_CollectTimes,
RichPic.Recommended as R_Recommended,
RichPic.SetTop as R_SetTop,
RichPic.Re as R_Re,Plus.Plus_ID as P_Plus_ID,
Plus.Source_ID as P_Source_ID,
Plus.Title as P_Title,
Plus.Description as P_Description,
Plus.URL as P_URL,
Plus.Price as P_Price,
Plus.L_Url as P_L_Url,
Plus.M_Url as P_M_Url,
Plus.S_Url as P_S_Url,
Plus.L_Width as P_L_Width,
Plus.L_Height as P_L_Height,
Plus.Code as P_Code,
Plus.CreateTime as P_CreateTime,
Plus.LastTime as P_LastTime,
Plus.Type as P_Type,
Plus.QuoteTimes as P_QuoteTimes,
Plus.CollectTimes as P_CollectTimes,
Plus.MouseOverTimes as P_MouseOverTimes,
Plus.ClickTimes as P_ClickTimes,
Plus.Recommended as P_Recommended,
Plus.Source as P_Source,
Plus.Classify as P_Classify,Users.FaceUrl as U_FaceUrl,
Users.NicName as U_NicName,
Users.Users_ID as U_Users_ID, PlusCopy.Plus_ID as PC_Plus_ID,
PlusCopy.L_X as PC_L_X,
PlusCopy.L_Y as PC_L_Y,row_number() over(partition by RichPic.RichPic_ID order by RichPic.RichPic_ID desc ) id
FROM Plus
INNER JOIN PlusCopy ON Plus.Plus_ID = PlusCopy.Plus_ID
INNER JOIN RichPic ON RichPic.RichPic_ID=PlusCopy.RichPic_ID
INNER JOIN Users ON RichPic.Users_ID =Users.Users_ID
where Plus.Plus_ID=1070
)a
where id=1
order by a.R_RichPic_ID desc