数据表如下所示:
A B C D
A1 B1 C1 D1
A2 B2 C2 D1
A3 B3 C3 D2
A4 B4 C4 D2要求查询结果如下:
A B C D
A1 B1 C1 D1
A3 B3 C3 D2也就是说D字段下只要有相同的就任选一条数据来显示。
A B C D
A1 B1 C1 D1
A2 B2 C2 D1
A3 B3 C3 D2
A4 B4 C4 D2要求查询结果如下:
A B C D
A1 B1 C1 D1
A3 B3 C3 D2也就是说D字段下只要有相同的就任选一条数据来显示。
select * from tablename where a in(select max(a) from tablename group by d)
XX为表名,没试过,应该是这样的。
declare @t table
(
A VARCHAR(50),
B VARCHAR(50),
C VARCHAR(50),
D VARCHAR(50)
)INSERT INTO @T VALUES('A1','B1','C1','D1')
INSERT INTO @T VALUES('A2','B2','C2','D1')
INSERT INTO @T VALUES('A3','B3','C3','D2')
INSERT INTO @T VALUES('A4','B4','C4','D2')
select * from @T where A in(select max(A) from @T group by D)
(
A VARCHAR(50),
B VARCHAR(50),
C VARCHAR(50),
D VARCHAR(50)
)INSERT INTO @T VALUES('A1','B1','C1','D1')
INSERT INTO @T VALUES('A2','B2','C2','D1')
INSERT INTO @T VALUES('A3','B3','C3','D2')
INSERT INTO @T VALUES('A4','B4','C4','D2')
select * from @T where A in(select MIN(A) from @T group by D)
select * from 表名 where A in (select max(A) from 表名 group by D)
然后用邮标循环这个表。select top 1 记录放到另一个临时表。然后返回这个临时表。
A字段下面的值可能一样,也可能不一样
其他B,C字段也是如此,可能一样也可能不一样
用存储过程我也知道怎么处理。
现在就想知道SQL语句怎么实现,因为这样改动程序很小的。
FROM shiyanbiao
WHERE (di4lie IN
(SELECT MIN(di4lie)
FROM shiyanbiao))
你要取最大的di4lie就是
SELECT *
FROM shiyanbiao
WHERE (di4lie IN
(SELECT MAX(di4lie)
FROM shiyanbiao))
只能用Sqldatareader dr来控制
例如:
if(dr.read()) //这个DR.READ就是来控制查询的,你可以用2次查询来搞定,一次查询不可能因为没有其他的判断
{ 条件,这个地方是程序逻辑上显示的问题,而不是查询的问题
*************
}
FROM
(
SELECT *, ROW_NUMBER(PARTITION BY D ORDER BY D) AS CNT
FROM TABLE
) AS A
WHERE CNT =1
我用的数据库是SQL2000哦,不是SQL2005
select distinct A,B,C,Tbl.D
from Tbl,
(select min(cast(A as varchar(4)) --A如果为int转换为字符
+'列分隔符'
+B
+'列分隔符'
+C) as flag,
D
from Tbl
group by D) subTbl
where cast (A as varchar(4))+'列分隔符'+B+'列分隔符'+C = flag
and Tbl.D= subTbl.D严格的来讲要把A,B,C分别用特殊字符补足各自的位数再转成字符型想加
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.[/color]
inner join (select distinct table2.d from yourtable table2)
table3
on table1.D = table3.D