SELECT distinct biaohao,factory,xinghao,dianliu,hege
FROM hbiao
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
ORDER BY biaohao,factory,xinghao,dianliu,hege
我想查找出以biaohao为索引的所有第一次出现的记录。用上面的语句不能实现,请问有谁能做到吗?
FROM hbiao
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
ORDER BY biaohao,factory,xinghao,dianliu,hege
我想查找出以biaohao为索引的所有第一次出现的记录。用上面的语句不能实现,请问有谁能做到吗?
解决方案 »
- ODBC访问mysql的问题
- delphi中dbgrid输入新数据时判断是否与以前内容重复的问题
- 在三层结构中,怎样在服务器端停止某个客户的连接,客户端通过TSocketConnection连接服务端
- 有关组件图标的一个简单问题
- 请问要怎样创建文件夹
- 如何将Excel中的内容导入到SQL Server数据库中???
- 关于QREPORT的RICHEDIT控件的行距设置问题??
- 医院管理系统的问题,多谢up!
- 串口通信问题,急急急!
- SQL server2000 表的查询
- 我想在我对DBgrid进行修改之后,dbgrid里面的数据能自动刷新显示我修改后的数据,请问该怎么做呢?
- 用QuickReport打印,如果打印机没有连接上,打印会出现?
SELECT biaohao,factory,xinghao,dianliu,hege
FROM hbiao
where Biaohao in
(
SELECT biaohao
FROM WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
)
ORDER BY biaohao
FROM hbiao
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
Group BY biaohao
试试这个^_^SELECT distinct biaohao,MAX(jyriqi) AS jyriqi
FROM hbiao
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
ORDER BY biaohao
FROM hbiao
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
Group BY biaohao having(count(biaohao)=1)用having count(biaohao)=1 可以找出不重复Biaohao的记录,但不包括重复biaohao而第一次出现的记录
select *from hbiao z where exists(select *from (select min(bianhao) as bianhao1 from hbiao group by
biaohao,factory,xinghao,dianliu,hege) where bianhao1=z.bianhao)就搞定
biaohao factory xinghao dianliu hege jyriqi
000001 绍兴 DD202 5(30) F 2003-10-01
000002 绍兴 DD202 5(30) F 2003-10-01
000001 绍兴 DD202 5(30) T 2003-10-02
000003 绍兴 DD202 5(30) F 2003-10-01
000004 绍兴 DD202 5(30) F 2003-10-20
000002 绍兴 DD202 5(30) T 2003-10-20
000005 绍兴 DD202 5(30) T 2003-10-21
我要的结果是:
000001 绍兴 DD202 5(30) F 2003-10-01
000002 绍兴 DD202 5(30) F 2003-10-01
000003 绍兴 DD202 5(30) F 2003-10-01
000004 绍兴 DD202 5(30) F 2003-10-20
000005 绍兴 DD202 5(30) T 2003-10-21
FROM hbiao
where Biaohao in
(
SELECT distinct biaohao
FROM WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
)
ORDER BY biaohao
这样好象可以
SELECT biaohao,factory,xinghao,dianliu,hege
FROM hbiao
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01') group by biaohao
ORDER BY biaohao,jyriqi,factory,xinghao,dianliu,hege
to pdbird(老巢) :from 子句错误.
FROM hbiao
where jyriqi in
(
SELECT biaohao,min(jyriqi)jyriqi biaohao
FROM WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
group by biaohao
)
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')----------------
where ...in里是找出 以找出以biaohao为分组的最小日期数据
把:SELECT biaohao,min(jyriqi)jyriqi biaohao
改成:SELECT biaohao,min(jyriqi)jyriqi
SELECT distinct biaohao,factory,xinghao,dianliu,hege
FROM hbiao a
where exists
(
select * from (
SELECT biaohao,min(jyriqi)jyriqi
FROM WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
group by biaohao
) b
WHERE (a.factory = b.factory) AND (a.jyriqi=b.jyriqi)
)
FROM hbiao a,
(
SELECT biaohao,min(jyriqi) as jyriqi
FROM WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
group by biaohao
) b
WHERE (a.biaohao=b.biaohao and a.factory = b.factory) AND (a.jyriqi=b.jyriqi)
我用的是sqlserver2000数据库.
子查询中from后为什么没有表名?这样要提示错误,加上呢,得的结果却又不对。
原表数据记录如:
biaohao factory xinghao dianliu hege jyriqi
000001 绍兴 DD202 5(30) F 2003-10-01
000002 绍兴 DD202 5(30) F 2003-10-01
000001 绍兴 DD202 5(30) T 2003-10-02
000003 绍兴 DD202 5(30) F 2003-10-01
000001 绍兴 DD202 5(30) T 2003-10-01
000004 绍兴 DD202 5(30) F 2003-10-20
000002 绍兴 DD202 5(30) T 2003-10-20
000005 绍兴 DD202 5(30) T 2003-10-21
000002 绍兴 DD202 5(30) F 2003-10-20
000005 绍兴 DD202 5(30) F 2003-10-21 我要的结果是:
000001 绍兴 DD202 5(30) F 2003-10-01
000002 绍兴 DD202 5(30) F 2003-10-01
000003 绍兴 DD202 5(30) F 2003-10-01
000004 绍兴 DD202 5(30) F 2003-10-20
000005 绍兴 DD202 5(30) T 2003-10-21
所得结果全部是第一次出现的记录。
SELECT
*
FROM
yourtable t1
INNER JOIN
(
SELECT
biaohao, factory, xinghao, dianliu , min(jyfiqi) AS jyfiqi
FROM
yourtable
GROUP BY
biaohao, factory, xinghao, dianliu , min(jyfiqi)
) AS t2
ON t1.biaohao = t2.biaohao AND t1.factory = t2.factory
t1.xinghao = t2.xinghao AND t1.dianlin = t2.dianliu
AND t1.yffiqi = t2. jyfiqi
where A.jyfiqi=(select top 1 B.jyriqi from hbiao B where B.hbiao=A.hbiao)--if you want select min(jyriqi) please change it
select distinct * from hbiao A
where A.jyfiqi=(select top 1 B.jyriqi from hbiao B where B.hbiao=A.hbiao and (B.factory = '绍兴') AND (B.jyriqi >= '2003-10-01')
) and (A.factory = '绍兴') AND (A.jyriqi >= '2003-10-01')
FROM hbiao
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01') AND (id IN (SELECT distinct biaohao FROM hbiao))
ORDER BY biaohao,factory,xinghao,dianliu,hege
biaohao factory xinghao dianliu hege jyriqi
000001 绍兴 DD202 5(30) F 2003-10-01
000001 绍兴 DD202 5(30) T 2003-10-01
000002 绍兴 DD202 5(30) F 2003-10-01
000003 绍兴 DD202 5(30) F 2003-10-01
000004 绍兴 DD202 5(30) F 2003-10-20
000005 绍兴 DD202 5(30) T 2003-10-21
000005 绍兴 DD202 5(30) F 2003-10-21
显然还有biaohao相同的数据
你到底试了我的没有?
SELECT distinct biaohao,MAX(jyriqi) AS jyriqi
FROM hbiao
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
ORDER BY biaohao
你可能还没理解我意思,我的目的是要取biaohao第一次出现的所有数据,数据取出后biaohao不能重复。而你的结果只有biaohao和jyriqi.
这回你试试~
SELECT distinct biaohao,MAX(jyriqi) AS jyriqi,factory ,xinghao, dianliu, hege
FROM hbiao
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
ORDER BY biaohao
你的方法得来的结果肯定有biaohao重复的记录,我第一次就是这样做的。现说后面不能用order by ,只能用group by
你看看这个
多个字段时:
SELECT DISTINCT Field1, MAX(Field2) AS Field2
FROM table1
GROUP BY Field1单个字段时:
SELECT DISTINCT Field1
FROM table1
这样再试一下^^
SELECT distinct biaohao,jyriqi,Max(factory) as factory ,xinghao, dianliu, hege
FROM hbiao
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
GROUP BY biaohao
应该写成
SELECT distinct biaohao,jyriqi,Max(factory) as factory ,xinghao, dianliu, hege
FROM hbiao
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
GROUP BY biaohao,jyriqi,factory,xinghao,dianliu,hege
才没有语句错,但不是我要的结果。
SELECT distinct biaohao,Max(jyriqi) as jyriqi,Max(factory) as factory ,Max(xinghao) as xinghao, Max(dianliu) as dianliu, Max(hege) as hege
FROM hbiao
WHERE (factory = '绍兴') AND (jyriqi >= '2003-10-01')
GROUP BY biaohao
单子;
据你所说,一天中你输入多条,那你的字段就不够了;应该还加个时间才行;
这样就可以区分最早的一天中的第一条单子了
写法一样................................
这样做是否把原数据给更改了,虽然biaohao是唯一,但我想得到第一条原始记录中的原数据.比如
biaohao factory xinghao dianliu hege jyriqi
000001 绍兴 DD202 5(30) F 2003-10-01
000002 绍兴 DD202 5(30) F 2003-10-01
000001 绍兴 DD202 5(30) T 2003-10-02
000003 绍兴 DD202 5(30) F 2003-10-01
000001 绍兴 DD202 5(30) T 2003-10-01
000004 绍兴 DD202 5(30) F 2003-10-20
000002 绍兴 DD202 5(30) T 2003-10-20
000005 绍兴 DD202 5(30) T 2003-10-21
000002 绍兴 DD202 5(30) F 2003-10-20
000005 绍兴 DD202 5(30) F 2003-10-21
用你的方法得到的结果是:
000001 绍兴 DD202 5(30) T 2003-10-01
000002 绍兴 DD202 5(30) F 2003-10-01
000003 绍兴 DD202 5(30) F 2003-10-01
000004 绍兴 DD202 5(30) F 2003-10-20
000005 绍兴 DD202 5(30) T 2003-10-21
很显然,000001第一次出现的hege结果是F,而非T.
下面的这个?
000001 绍兴 DD202 5(30) F 2003-10-01
000002 绍兴 DD202 5(30) F 2003-10-01
000001 绍兴 DD202 5(30) T 2003-10-02
000003 绍兴 DD202 5(30) F 2003-10-01
000001 绍兴 DD202 5(30) T 2003-10-01
000004 绍兴 DD202 5(30) F 2003-10-20
000002 绍兴 DD202 5(30) T 2003-10-20
000005 绍兴 DD202 5(30) T 2003-10-21
000002 绍兴 DD202 5(30) F 2003-10-20
000005 绍兴 DD202 5(30) F 2003-10-21