我数据库有三个表,分别是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
结果显示有问题:只显示签到的记录,而且签到时间与签退时间一样。高手看看这个语句该如何写!
结果显示有问题:只显示签到的记录,而且签到时间与签退时间一样。高手看看这个语句该如何写!
同一个学号同一个IP应该有重复记录吧
同一个学号同一个IP同一天是不是又重复记录?
那个组合是唯一的
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.TimeDROP 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.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
按LZ的语句执行的结果,看起来还不错
*/
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
哪里有错吗?
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 这样行不
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表哪来的
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
\这样会怎么样
{
//获取连接数据库字符串
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();
我找到你的错误了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 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 行受影响)
看成功不
DataBinding:“System.Data.DataRowView”不包含名为“id”的属性。” 这个错误 ID属性不是三张表里的吧?
this.GridView1.DataKeyNames = new string[] { "id" };
你应该错在这里 具体我就不清楚了