记录合并问题!高手进!

FeelTouch Labs
博客专家认证
2009-07-17 01:14:28
我数据库有三个表,分别是T1,T2,T3其中T1,T2中的数据字段值都一样为ID Number(学号) IP(就是IP地址)Time(签到时间)类型(T1T2分别存的是签到,签退字),T3表是学生表(包括学生的学号,姓名信息),因为T1,T2都是成对出现的(即签到签退),所以我需要从这三个表中取数据,使显示的每一条记录为 姓名,学号,IP(签到签退的IP是一样的),签到时间,签退时间,或者显示为每个学生的一对签到签退为同一颜色也可(就是让我们容易的知道这是一次考勤),我的SQL语句是:select Pre0.Number,Pre0.IP,Pre0.Time,min(Pre1.Time) from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time and Pre1.Style='簽退' where Pre0.Style='簽到' group by Pre0.Number,Pre0.IP, Pre0.Time
结果显示有问题:只显示签到的记录,而且签到时间与签退时间一样。高手看看这个语句该如何写!
...全文
202 40 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
40 条回复
切换为时间正序
请发表友善的回复…
发表回复
feixianxxx 2009-07-18
  • 打赏
  • 举报
回复
[Quote=引用 29 楼 fengqiyunran 的回复:]
谢谢,我再试试!
[/Quote]
OK?
FeelTouch Labs 2009-07-18
  • 打赏
  • 举报
回复
谢谢,我再试试!
FeelTouch Labs 2009-07-18
  • 打赏
  • 举报
回复
非常感谢feixianxxx,我的QQ是872977471,能交个朋友吗?共同学习,我应该用换一种思路来解决问题了,但我不否认这种绝对是一种好方法,虽然我没有试成功!谢谢!
feixianxxx 2009-07-18
  • 打赏
  • 举报
回复
[Quote=引用 26 楼 fengqiyunran 的回复:]
我表里都是中文简体,程序里也是,如上面所示。但是就是...继续努力,不放弃
[/Quote]
可是我用你的代码 试过啦 可以出来结果的啊。。。(只需要改下字体 你自己看 下面的)

CREATE TABLE PRE0( 
NUMBER VARCHAR(100),
IP VARCHAR(20),
[TIME] DATETIME,
Style VARCHAR(10)
)
INSERT INTO PRE0
SELECT '20071632','127.0.0.1','2009-7-12 21:53:44','簽到' UNION ALL
SELECT '20071632','127.0.0.1','2009-7-12 21:53:44','簽到' UNION ALL
SELECT '20071632','127.0.0.1','2009-7-14 15:01:17','簽到' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 19:51:57','簽到' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 19:52:23','簽到' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 19:52:57','簽到' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 19:53:22','簽到' UNION ALL
SELECT '20051936','127.0.0.1','2009-7-12 19:54:44','簽到'

CREATE TABLE PRE1(
NUMBER VARCHAR(100),
IP VARCHAR(20),
[TIME] DATETIME,
Style VARCHAR(10)
)
INSERT INTO PRE1
SELECT '20071632','127.0.0.1','2009-7-12 22:53:44','簽退' UNION ALL
SELECT '20071632','127.0.0.1','2009-7-12 22:53:44','簽退' UNION ALL
SELECT '20071632','127.0.0.1','2009-7-14 18:01:17','簽退' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 22:51:57','簽退' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 23:52:23','簽退' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 20:52:57','簽退' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 21:53:22','簽退' UNION ALL
SELECT '20051936','127.0.0.1','2009-7-12 20:54:44','簽退'
CREATE TABLE Member(
NUMBER VARCHAR(100),
[NAME] VARCHAR(100)
)
INSERT INTO Member
SELECT '20071632','A' UNION ALL
SELECT '2076011','B' UNION ALL
SELECT '20051936','C'


select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time)
from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time and Pre1.Style='簽退' inner join Member
ON Pre0.Number=Member.Number
where Pre0.Style='簽到' group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time


Number Name IP Time
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -------------------- ----------------------- -----------------------
20051936 C 127.0.0.1 2009-07-12 19:54:44.000 2009-07-12 20:54:44.000
20071632 A 127.0.0.1 2009-07-12 21:53:44.000 2009-07-12 22:53:44.000
20071632 A 127.0.0.1 2009-07-14 15:01:17.000 2009-07-14 18:01:17.000
2076011 B 127.0.0.1 2009-07-12 19:51:57.000 2009-07-12 20:52:57.000
2076011 B 127.0.0.1 2009-07-12 19:52:23.000 2009-07-12 20:52:57.000
2076011 B 127.0.0.1 2009-07-12 19:52:57.000 2009-07-12 20:52:57.000
2076011 B 127.0.0.1 2009-07-12 19:53:22.000 2009-07-12 20:52:57.000

(7 行受影响)
FeelTouch Labs 2009-07-18
  • 打赏
  • 举报
回复
我表里都是中文简体,程序里也是,如上面所示。但是就是...继续努力,不放弃
feixianxxx 2009-07-18
  • 打赏
  • 举报
回复
[Quote=引用 24 楼 fengqiyunran 的回复:]
我的程序代码, protected void bind()
    {
        //获取连接数据库字符串
        string S601_dns = WebConfigurationManager.ConnectionStrings["S601ConnectionString"].ToString();
        //创建连接对象
        SqlConnection S601_myConn = new SqlConnection(S601_dns);
        //打开连接对象
        S601_myConn.Open();
        //创建连接桥并从数据库检索数据
        SqlDataAdapter sda = new SqlDataAdapter("select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time) from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time  and Pre1.Style='签退' inner join  Member ON Pre0.Number=Member.Number where Pre0.Style='签到' group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time", S601_myConn);
        //创建数据集
        DataSet ds = new DataSet();
        //将从数据库检索的数据填充到数据表中
        sda.Fill(ds, "Attendance");
        //指定表的数据源
        this.GridView1.DataSource = ds.Tables["Attendance"];
        //指定主键
        this.GridView1.DataKeyNames = new string[] { "id" };
        //绑定到数据源
        this.GridView1.DataBind();
[/Quote]

我找到你的错误了

select Pre0.Number,Member.[Name],Pre0.IP,Pre0.Time,min(Pre1.Time)
from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time
and Pre1.Style='签退' inner join Member ON Pre0.Number=Member.Number
where Pre0.Style='簽到' 你的字体。。。你仔细看看 ‘签到’ 你表里是哪个字体
group by Pre0.Number,Member.[Name],Pre0.IP,Pre0.Time
FeelTouch Labs 2009-07-18
  • 打赏
  • 举报
回复
我的程序代码, protected void bind()
{
//获取连接数据库字符串
string S601_dns = WebConfigurationManager.ConnectionStrings["S601ConnectionString"].ToString();
//创建连接对象
SqlConnection S601_myConn = new SqlConnection(S601_dns);
//打开连接对象
S601_myConn.Open();
//创建连接桥并从数据库检索数据
SqlDataAdapter sda = new SqlDataAdapter("select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time) from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time and Pre1.Style='签退' inner join Member ON Pre0.Number=Member.Number where Pre0.Style='签到' group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time", S601_myConn);
//创建数据集
DataSet ds = new DataSet();
//将从数据库检索的数据填充到数据表中
sda.Fill(ds, "Attendance");
//指定表的数据源
this.GridView1.DataSource = ds.Tables["Attendance"];
//指定主键
this.GridView1.DataKeyNames = new string[] { "id" };
//绑定到数据源
this.GridView1.DataBind();
FeelTouch Labs 2009-07-18
  • 打赏
  • 举报
回复
[Quote=引用 11 楼 guguda2008 的回复:]
SQL code
CREATE TABLE PRE0(
NUMBER VARCHAR(100),
IP VARCHAR(20),
[TIME] DATETIME,
Style VARCHAR(10)
)
INSERT INTO PRE0
SELECT '20071632','127.0.0.1','2009-7-12 21:53:44','簽到' UNION ALL
SELECT '20071632','127.0.0.1','2009-7-12 21:53:44','簽到' UNION ALL
SELECT '20071632','127.0.0.1','2009-7-14 15:01:17','簽到' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 19:51:57','簽到' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 19:52:23','簽到' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 19:52:57','簽到' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 19:53:22','簽到' UNION ALL
SELECT '20051936','127.0.0.1','2009-7-12 19:54:44','簽到'

CREATE TABLE PRE1(
NUMBER VARCHAR(100),
IP VARCHAR(20),
[TIME] DATETIME,
Style VARCHAR(10)
)
INSERT INTO PRE1
SELECT '20071632','127.0.0.1','2009-7-12 22:53:44','簽退' UNION ALL
SELECT '20071632','127.0.0.1','2009-7-12 22:53:44','簽退' UNION ALL
SELECT '20071632','127.0.0.1','2009-7-14 18:01:17','簽退' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 22:51:57','簽退' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 23:52:23','簽退' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 20:52:57','簽退' UNION ALL
SELECT '2076011','127.0.0.1','2009-7-12 21:53:22','簽退' UNION ALL
SELECT '20051936','127.0.0.1','2009-7-12 20:54:44','簽退'
CREATE TABLE STU(
NUMBER VARCHAR(100),
[NAME] VARCHAR(100)
)
INSERT INTO STU
SELECT '20071632','A' UNION ALL
SELECT '2076011','B' UNION ALL
SELECT '20051936','C'
select Pre0.Number,STU.[NAME],Pre0.IP,Pre0.Time,min(Pre1.Time)
from Pre0
left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time and Pre1.Style='簽退'
INNER JOIN STU ON PRE0.NUMBER=STU.NUMBER
where Pre0.Style='簽到'
group by Pre0.Number,STU.[NAME],Pre0.IP, Pre0.Time

DROP TABLE PRE0
DROP TABLE PRE1
DROP TABLE STU

/*
20051936 C 127.0.0.1 2009-07-12 19:54:44.000 2009-07-12 20:54:44.000
20071632 A 127.0.0.1 2009-07-12 21:53:44.000 2009-07-12 22:53:44.000
20071632 A 127.0.0.1 2009-07-14 15:01:17.000 2009-07-14 18:01:17.000
2076011 B 127.0.0.1 2009-07-12 19:51:57.000 2009-07-12 20:52:57.000
2076011 B 127.0.0.1 2009-07-12 19:52:23.000 2009-07-12 20:52:57.000
2076011 B 127.0.0.1 2009-07-12 19:52:57.00 2009-07-12 20:52:57.000
2076011 B 127.0.0.1 2009-07-12 19:53:22.00 2009-07-12 20:52:57.000
按LZ的语句执行的结果,看起来还不错
*/
[/Quote]这就是我想要的结果,为什么11楼的好使我的就不好使呢?11楼还是按我的代码测试的呢!我提示的错误是“DataBinding:“System.Data.DataRowView”不包含名为“id”的属性。”
feixianxxx 2009-07-18
  • 打赏
  • 举报
回复
[Quote=引用 21 楼 fengqiyunran 的回复:]
谢谢feixianxxx及其他的所有的回答,又提示和第一次同样的错误,我还要尝试,虽然这问题看起来很棘手
[/Quote]
到底提示错什么来着
FeelTouch Labs 2009-07-18
  • 打赏
  • 举报
回复
谢谢feixianxxx及其他的所有的回答,又提示和第一次同样的错误,我还要尝试,虽然这问题看起来很棘手
FeelTouch Labs 2009-07-18
  • 打赏
  • 举报
回复
好的,我再试试!
feixianxxx 2009-07-18
  • 打赏
  • 举报
回复
select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time) 
from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time inner join Member ON Pre0.Number=Member.Number
group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time

\这样会怎么样
FeelTouch Labs 2009-07-18
  • 打赏
  • 举报
回复
提示 "' ' 附近有语法错误。"按照feixianxxx说得做,就差那么一点了,还解决不了.我还要再试试
FeelTouch Labs 2009-07-18
  • 打赏
  • 举报
回复
我试试,我的Member表就是学生信息表!
feixianxxx 2009-07-18
  • 打赏
  • 举报
回复
[Quote=引用 14 楼 fengqiyunran 的回复:]
照着十一楼说的做,提示"DataBinding:“System.Data.DataRowView”不包含名为“id”的属性。"我的代码是
select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time) from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time and Pre1.Style='签退' inner join  Member ON Pre0.Number=Member.Number where Pre0.Style='签到' group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time
哪里有错吗?
[/Quote]

select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time) 
from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time inner join Member ON Pre0.Number=Member.Number
where Pre0.Style='签到' and Pre1.Style='签退'
group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time
试试看 你的Member表哪来的
feixianxxx 2009-07-18
  • 打赏
  • 举报
回复
[Quote=引用 14 楼 fengqiyunran 的回复:]
照着十一楼说的做,提示"DataBinding:“System.Data.DataRowView”不包含名为“id”的属性。"我的代码是
select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time) from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time and Pre1.Style='签退' inner join  Member ON Pre0.Number=Member.Number where Pre0.Style='签到' group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time
哪里有错吗?
[/Quote]


select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time) 
from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time inner join Member ON Pre0.Number=Member.Number
where Pre0.Style='签到' and Pre1.Style='签退'
group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time
这样行不
FeelTouch Labs 2009-07-18
  • 打赏
  • 举报
回复
照着十一楼说的做,提示"DataBinding:“System.Data.DataRowView”不包含名为“id”的属性。"我的代码是
select Pre0.Number,Member.Name,Pre0.IP,Pre0.Time,min(Pre1.Time) from Pre0 left join Pre1 on Pre0.Number=Pre1.Number and Pre1.Time>=Pre0.Time and Pre1.Style='签退' inner join Member ON Pre0.Number=Member.Number where Pre0.Style='签到' group by Pre0.Number,Member.Name,Pre0.IP,Pre0.Time
哪里有错吗?
feixianxxx 2009-07-18
  • 打赏
  • 举报
回复
[Quote=引用 38 楼 fengqiyunran 的回复:]
谢谢你所有的回答,我找到解决办法了!把所有的分都给你!
[/Quote]
解决就好了
FeelTouch Labs 2009-07-18
  • 打赏
  • 举报
回复
这个语句绝对没有错!
FeelTouch Labs 2009-07-18
  • 打赏
  • 举报
回复
谢谢你所有的回答,我找到解决办法了!把所有的分都给你!
加载更多回复(18)

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧