我有一个关于ＳＱＬ语句的问题请教大家，在线等！！！ ( 积分: 100 )

１．数据表ＴＡＢＬＥ１中数据如下：
线别 工位 时间段 投入数量
Line M Time1 qty
L1 3 05:00-06:00 71
L1 3 01:00-02:00 115
L1 3 07:00-08:00 52
L1 3 20:30-21:00 25
L1 3 22:00-23:00 66
L1 3 03:00-04:00 72
L1 3 04:00-05:00 77
L1 3 23:00-24:00 82
L1 3 21:00-22:00 64
L1 3 06:00-07:00 54
L1 3 02:00-03:00 92
L1 2 05:00-06:00 50
L1 2 01:00-02:00 100
L1 2 07:00-08:00 60
L1 2 20:30-21:00 30
L1 2 22:00-23:00 46
L1 2 03:00-04:00 75
L1 2 04:00-05:00 80
L1 2 23:00-24:00 90
L1 2 21:00-22:00 35
L1 2 06:00-07:00 63
L1 2 02:00-03:00 91
２．我想利用ＳＱＬ语句实现下面的结果：
Line Time1 qty_m2 qty_m3
L1 05:00-06:00 71 50
L1 01:00-02:00 115 100
L1 07:00-08:00 52 60
L1 20:30-21:00 25 30
L1 22:00-23:00 66 46
L1 03:00-04:00 72 75
L1 04:00-05:00 77 80
L1 23:00-24:00 82 90
L1 21:00-22:00 64 35
L1 06:00-07:00 54 63
L1 02:00-03:00 92 91
感谢大哥大姐的帮忙！！！！！

你试试下面的语句
select distinct time,(select qty from table1 where time1=a.time and m=3) as qty_m2,(select qty from table1 where time1=a.time and m=2) as qty_m3
from table1 a

select distinct Line, M, Time1, qty_m2 , qty_m3
from ＴＡＢＬＥ１
left join (select Line, M, Time1, qty as qty_m2
where Line='L1'
and M=2
and Time1='05:00-06:00') A on A.Line=ＴＡＢＬＥ１.Line
and A.Time1=ＴＡＢＬＥ１.Time1
and A.m=ＴＡＢＬＥ１.m
left join (select Line, M, Time1, qty as qty_m3
where Line='L1'
and M=3
and Time1='05:00-06:00') b on B.Line=ＴＡＢＬＥ１.Line
and B.Time1=ＴＡＢＬＥ１.Time1
and B.m=ＴＡＢＬＥ１.m

表1 M_CLASS
LINE TIME2 M M_COUNT
L1 20:30 1 1
L1 00:30 3 1
L1 00:40 3 12
L1 00:50 3 14
L1 01:00 3 14
L1 01:10 3 10
L1 01:20 3 17
L1 01:30 3 17
L1 01:40 3 15
L1 01:50 3 15
L1 02:00 3 15
L1 02:10 3 15
L1 02:20 3 16
L1 02:30 3 16
L1 02:40 3 17
L1 02:50 3 13
L1 03:10 3 15
L1 03:20 3 16
L1 03:30 3 11
L1 03:40 3 17
L1 03:50 3 13
L1 04:00 3 12
L1 04:10 3 10
L1 04:20 3 12
L1 04:30 3 11
L1 04:40 3 16
L1 04:50 3 16
L1 05:10 3 15
L1 05:20 3 14
L1 05:30 3 17
L1 05:40 3 14
L1 05:50 3 11
L1 06:00 3 6
L1 06:10 3 9
L1 06:20 3 7
L1 06:30 3 10
L1 06:40 3 12
L1 06:50 3 10
L1 07:00 3 12
L1 07:10 3 12
L1 07:20 3 14
L1 07:30 3 7
L1 07:40 3 7
L1 20:40 3 12
L1 20:50 3 13
L1 21:00 3 14
L1 21:10 3 8
L1 21:20 3 13
L1 21:30 3 9
L1 21:40 3 13
L1 21:50 3 7
L1 22:10 3 15
L1 22:20 3 10
L1 22:30 3 14
L1 22:40 3 13
L1 22:50 3 14
L1 23:00 3 12
L1 23:10 3 16
L1 23:20 3 12
L1 23:30 3 15
L1 23:40 3 16
L1 23:50 3 11
表2 TIME_CLASS
ID_NO TIME_CLASS PLACE
1 08:00 1
2 08:10 2
3 08:20 3
4 08:30 4
5 08:40 5
6 08:50 6
7 09:00 7
8 09:10 8
9 09:20 9
10 09:30 10
11 09:40 11
12 09:50 12
13 10:00 13
14 10:10 14
15 10:20 15
16 10:30 16
17 10:40 17
18 10:50 18
19 11:00 19
20 11:10 20
21 11:20 21
22 11:30 22
23 11:40 23
24 11:50 24
26 12:10 26
27 12:20 27
28 12:30 28
30 12:50 30
31 13:00 31
32 13:10 32
34 13:30 34
35 13:40 35
36 13:50 36
37 14:00 37
38 14:10 38
39 14:20 39
42 14:50 42
43 15:00 43
45 15:20 45
46 15:30 46
47 15:40 47
48 15:50 48
49 16:00 49
50 16:10 50
51 16:20 51
52 16:30 52
53 16:40 53
54 16:50 54
55 17:00 55
56 17:10 56
57 17:20 57
58 17:30 58
59 17:40 59
63 18:20 63
65 18:40 65
66 18:50 66
67 19:00 67
68 19:10 68
69 19:20 69
70 19:30 70
73 20:00 73
74 20:10 74
77 20:40 77
80 21:10 80
81 21:20 81
82 21:30 82
83 21:40 83
84 21:50 84
85 22:00 85
86 22:10 86
87 22:20 87
88 22:30 88
89 22:40 89
90 22:50 90
91 23:00 91
92 23:10 92
93 23:20 93
94 23:30 94
95 23:40 95
96 23:50 96
97 00:00 97
98 00:10 98
100 00:30 100
101 00:40 101
102 00:50 102
103 01:00 103
104 01:10 104
105 01:20 105
106 01:30 106
107 01:40 107
108 01:50 108
109 02:00 109
110 02:10 110
111 02:20 111
113 02:40 113
115 03:00 115
116 03:10 116
117 03:20 117
118 03:30 118
121 04:00 121
122 04:10 122
123 04:20 123
124 04:30 124
126 04:50 126
127 05:00 127
130 05:30 130
131 05:40 131
132 05:50 132
133 06:00 133
134 06:10 134
135 06:20 135
136 06:30 136
137 06:40 137
138 06:50 138
139 07:00 139
141 07:20 141
142 07:30 142
143 07:40 143
144 07:50 144
25 12:00 25
29 12:40 29
33 13:20 33
40 14:30 40
41 14:40 41
44 15:10 44
60 17:50 60
61 18:00 61
62 18:10 62
64 18:30 64
71 19:40 71
72 19:50 72
75 20:20 75
76 20:30 76
78 20:50 78
79 21:00 79
99 00:20 99
112 02:30 112
114 02:50 114
119 03:40 119
120 03:50 120
125 04:40 125
128 05:10 128
129 05:20 129
140 07:10 140
145 08:00 145
通过下列语句想实现的效果没有实现
select distinct time2,
ISNULL((select M_COUNT from M_CLASS where time2=a.time2 and m=1),0) as qty_m1,
ISNULL((select M_COUNT from M_CLASS where time2=a.time2 and m=2),0) as qty_m2,
ISNULL((select M_COUNT from M_CLASS where time2=a.time2 and m=3),0) as qty_m3,
LINE,B.PLACE
from M_CLASS a JOIN TIME_CLASS b ON A.TIME2=B.TIME_CLASS
ORDER BY B.PLACE
查询语句结果如下：
TIME2 QTY_M1 QTY_M2 QTY_ME LINE PLACE
20:30 1 0 0 L1 76
20:40 0 0 12 L1 77
20:50 0 0 13 L1 78
21:00 0 0 14 L1 79
21:10 0 0 8 L1 80
21:20 0 0 13 L1 81
21:30 0 0 9 L1 82
21:40 0 0 13 L1 83
21:50 0 0 7 L1 84
22:10 0 0 15 L1 86
22:20 0 0 10 L1 87
22:30 0 0 14 L1 88
22:40 0 0 13 L1 89
22:50 0 0 14 L1 90
23:00 0 0 12 L1 91
23:10 0 0 16 L1 92
23:20 0 0 12 L1 93
23:30 0 0 15 L1 94
23:40 0 0 16 L1 95
23:50 0 0 11 L1 96
00:30 0 0 1 L1 100
00:40 0 0 12 L1 101
00:50 0 0 14 L1 102
01:00 0 0 14 L1 103
01:10 0 0 10 L1 104
01:20 0 0 17 L1 105
01:30 0 0 17 L1 106
01:40 0 0 15 L1 107
01:50 0 0 15 L1 108
02:00 0 0 15 L1 109
02:10 0 0 15 L1 110
02:20 0 0 16 L1 111
02:30 0 0 16 L1 112
02:40 0 0 17 L1 113
02:50 0 0 13 L1 114
03:10 0 0 15 L1 116
03:20 0 0 16 L1 117
03:30 0 0 11 L1 118
03:40 0 0 17 L1 119
03:50 0 0 13 L1 120
04:00 0 0 12 L1 121
04:10 0 0 10 L1 122
04:20 0 0 12 L1 123
04:30 0 0 11 L1 124
04:40 0 0 16 L1 125
04:50 0 0 16 L1 126
05:10 0 0 15 L1 128
05:20 0 0 14 L1 129
05:30 0 0 17 L1 130
05:40 0 0 14 L1 131
05:50 0 0 11 L1 132
06:00 0 0 6 L1 133
06:10 0 0 9 L1 134
06:20 0 0 7 L1 135
06:30 0 0 10 L1 136
06:40 0 0 12 L1 137
06:50 0 0 10 L1 138
07:00 0 0 12 L1 139
07:10 0 0 12 L1 140
07:20 0 0 14 L1 141
07:30 0 0 7 L1 142
07:40 0 0 7 L1 143

想要实现的结果如下：
TIME2 QTY_M1 QTY_M2 QTY_ME LINE PLACE
20:30 1 0 0 L1 76
20:40 0 0 12 L1 77
20:50 0 0 13 L1 78
21:00 0 0 14 L1 79
21:10 0 0 8 L1 80
21:20 0 0 13 L1 81
21:30 0 0 9 L1 82
21:40 0 0 13 L1 83
21:50 0 0 7 L1 84
[red]22:00 0 0 0 L1 85[/red]
22:10 0 0 15 L1 86
22:20 0 0 10 L1 87
22:30 0 0 14 L1 88
22:40 0 0 13 L1 89
22:50 0 0 14 L1 90
23:00 0 0 12 L1 91
23:10 0 0 16 L1 92
23:20 0 0 12 L1 93
23:30 0 0 15 L1 94
23:40 0 0 16 L1 95
23:50 0 0 11 L1 96
[red]00:00 0 0 0 L1 97
00:10 0 0 0 L1 98
00:20 0 0 0 L1 99[/red]
00:30 0 0 1 L1 100
00:40 0 0 12 L1 101
00:50 0 0 14 L1 102
01:00 0 0 14 L1 103
01:10 0 0 10 L1 104
01:20 0 0 17 L1 105
01:30 0 0 17 L1 106
01:40 0 0 15 L1 107
01:50 0 0 15 L1 108
02:00 0 0 15 L1 109
02:10 0 0 15 L1 110
02:20 0 0 16 L1 111
02:30 0 0 16 L1 112
02:40 0 0 17 L1 113
02:50 0 0 13 L1 114
[brown]03:00 0 0 0 L1 115[/brown]
03:10 0 0 15 L1 116
03:20 0 0 16 L1 117
03:30 0 0 11 L1 118
03:40 0 0 17 L1 119
03:50 0 0 13 L1 120
04:00 0 0 12 L1 121
04:10 0 0 10 L1 122
04:20 0 0 12 L1 123
04:30 0 0 11 L1 124
04:40 0 0 16 L1 125
04:50 0 0 16 L1 126
[blue]05:00 0 0 0 L1 127[/blue]
05:10 0 0 15 L1 128
05:20 0 0 14 L1 129
05:30 0 0 17 L1 130
05:40 0 0 14 L1 131
05:50 0 0 11 L1 132
06:00 0 0 6 L1 133
06:10 0 0 9 L1 134
06:20 0 0 7 L1 135
06:30 0 0 10 L1 136
06:40 0 0 12 L1 137
06:50 0 0 10 L1 138
07:00 0 0 12 L1 139
07:10 0 0 12 L1 140
07:20 0 0 14 L1 141
07:30 0 0 7 L1 142
07:40 0 0 7 L1 143

select line,time sum(case m when 2 then m else 0 end ) as qty_m2, sum(case m when 3 then m else 0 end ) as qty_m3 from ＴＡＢＬＥ１ group by line, Time1

select a.line,a.time1,(select qty from test where m=3 and time1=a.time1) as qty_m2,(select qty from test where m=2 and time1=a.time1) as qty_m3 from
(select line,time1 from test group by line,time1) a

44这个

好像是交叉表的格式

我操，这个世界没有天理了～～～～～ MD,老虎不发威,当我是病猫, 先骂人的倒有理了 我可是从来不主动得罪人的 谁要是欺负人,那就不客气了 明天把这个工具开个源,叫大家都来用用 请问你们有什么拿的出手的?自己写的东西? 说出来叫大爷我长长见识？ 别不会是一群耍嘴皮子的废物吧？ 需要的请关注我的 blog http://hi.baidu.com/earthsearch