MYSQL 查询语句问题

貌似明白你的意思了。这是我写的测试，你看是不是这个意思：

准备测试环境：

CREATE TABLE `tt` (

`id` int(11) NOT NULL default '0',

`value` varchar(2) default '',

PRIMARY KEY (`id`)

)

insert into tt values(1,'a')

insert into tt values(2,'b')

insert into tt values(3,'c')

insert into tt values(4,'a')

insert into tt values(5,'b')

开始：

1，怎么样得到相同的值的和 a有相同的 b有相同的 a b 为两个2

mysql>select tt1.value,count(*) from tt as tt1 ,tt as tt2 where tt1.id!=tt2.id

and tt1.value=tt2.value group by tt1.value

+-------+----------+

| value | count(*) |

+-------+----------+

| a |2 |

| b |2 |

+-------+----------+

2 rows in set (0.00 sec)

// 得到相同的次数

mysql>select count(*) from (select tt1.value from tt as tt1 ,tt as tt2 where tt1.id!=tt2.id and tt1.value=tt2.value group by tt1.value )as tt3

+----------+

| count(*) |

+----------+

|2 |

+----------+

1 row in set (0.00 sec)

// 得到不同的次数

mysql>select count(*) from (select tt1.value from tt as tt1 ,tt as tt2 where tt1.id!=tt2.id and tt1.value!=tt2.value group by tt1.value )as tt3

+----------+

| count(*) |

+----------+

|3 |

+----------+

1 row in set (0.00 sec)

2，和不同值的和上面得到一个2 然后用 最大的ID减 5 -2 = 3

这里不太明白 不同值的和应该是3 也就是abc3种了，相同的和是2 ，

无论如何，2种情况我都写下来

mysql>select max(id)- (select count(*) from (select tt1.value from tt as tt1 ,tt as tt2 where tt1.id!=tt2.id and tt1.value=tt2.value group by tt1.value )as tt3) from tt

+-------------------------------------------------------------------------------

-----------------------------------------------------------------------+

| max(id)- (select count(*) from (select tt1.value from tt as tt1 ,tt as tt2 wh

ere tt1.id!=tt2.id and tt1.value=tt2.value group by tt1.value )as tt3) |

+-------------------------------------------------------------------------------

-----------------------------------------------------------------------+

|

3 |

+-------------------------------------------------------------------------------

-----------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>select max(id) - (select count(*) from (select tt1.value from tt as tt1 ,tt as tt2 where tt1.id!=tt2.id and tt1.value!=tt2.value group by tt1.value )as tt3) from tt

+-------------------------------------------------------------------------------

------------------------------------------------------------------------+

| max(id) - (select count(*) from (select tt1.value from tt as tt1 ,tt as tt2 wh

ere tt1.id!=tt2.id and tt1.value!=tt2.value group by tt1.value )as tt3) |

+-------------------------------------------------------------------------------

------------------------------------------------------------------------+

|

2 |

+-------------------------------------------------------------------------------

------------------------------------------------------------------------+

1 row in set (0.00 sec)

你看你的意思是不是这个

你这个题目字数真多，第一个创建表，按固定格式写就行了，第二插入数据用insert，最基本的语句，第三个update，修改语句，也是最基本的，第四个和第五个都是条件删除delete，后面基本都是基础的查询，字太多了，看着有点乱

/*

闲着没事，瞅瞅百度上的问题，今天天晚了，先解决一个，另一个明儿个再说了！

第二道题也算已经搞定了！

环境 : mysql Ver 14.12 Distrib 5.0.45, for Win32 (ia32)

参考 :

exist与in 的区别

http://blog.csdn.net/change888/archive/2008/03/31/2232778.aspx

*/

/*********************************问题 1 **************************************/

drop table if exists s

create table if not exists s (s varchar(32), sn varchar(32), sd varchar(32),

sa int)

insert into s values ('s1', '朱', '开发本部', 23)

insert into s values ('s2', '牛', '人事部', 25)

insert into s values ('s3', '杨', '财务部', 26)

insert into s values ('s4', '马', '开发本部', 22)

insert into s values ('s5', '吕', '人事部', 27)

insert into s values ('s6', '于', '开发本部', 28)

insert into s values ('s7', '侯', '开发本部', 28)

drop table if exists c

create table if not exists c (c varchar(32), cn varchar(32))

insert into c values ('c1', '软件工程')

insert into c values ('c2', '计算机技术与科学')

insert into c values ('c3', '车辆工程')

drop table if exists sc

create table if not exists sc (s varchar(32), c varchar(32))

insert into sc values ('s1', 'c1')

insert into sc values ('s1', 'c2')

insert into sc values ('s1', 'c3')

insert into sc values ('s2', 'c1')

insert into sc values ('s2', 'c3')

insert into sc values ('s3', 'c2')

insert into sc values ('s4', 'c2')

insert into sc values ('s4', 'c3')

insert into sc values ('s5', 'c1')

insert into sc values ('s6', 'c3')

/* 1. 查询选修课程名称为 “软件工程” 的学员学号和姓名 */

select s.s '学号', s.sn '姓名' from s where s.s in

(select sc.s from sc where sc.c in

(select c.c from c where c.cn = '软件工程'))

/* 2. 查询选修课程编号为 “C2” 的学员姓名和所属单位 */

select s.sn '姓名', s.sd '所属单位' from s where s.s in

(select sc.s from sc where sc.c = 'C2')

/* 3. 查询选修课程编号 不 为 “C2” 的学员姓名和所属单位 */

select s.sn '姓名', s.sd '所属单位' from s where

s.s not in (select sc.s from sc where sc.c = 'C2')

and

s.s in (select sc.s from sc)

/* 4. 查询选修全部课程的学员姓名和所属单位 */

select s.sn '姓名', s.sd '所属单位' from s where

(select count(DISTINCT sc.c) from sc where sc.s = s.s)

=

(select count(DISTINCT c.c) from c )

/* 5. 查询选修了课程的学员人数 */

select count(DISTINCT sc.s) '人数' from sc

/* 6. 查询选修课程 >= 2 门的学员学号和所属单位 （不得不用 CASE 语句了）*/

select s.sn '姓名', s.sd '所属单位' from s where s.s in

(select CASE WHEN count(DISTINCT sc.c) >=2 THEN sc.s END from sc group by sc.s )

/* 运行结果

------------------------------------1

+------+------+

| 学号 | 姓名 |

+------+------+

| s1 | 朱 |

| s2 | 牛 |

| s5 | 吕 |

+------+------+

------------------------------------2

+------+----------+

| 姓名 | 所属单位 |

+------+----------+

| 朱 | 开发本部 |

| 杨 | 财务部 |

| 马 | 开发本部 |

+------+----------+

------------------------------------3

+------+----------+

| 姓名 | 所属单位 |

+------+----------+

| 牛 | 人事部 |

| 吕 | 人事部 |

| 于 | 开发本部 |

+------+----------+

------------------------------------4

+------+----------+

| 姓名 | 所属单位 |

+------+----------+

| 朱 | 开发本部 |

+------+----------+

------------------------------------5

+------+

| 人数 |

+------+

|6 |

+------+

------------------------------------6

+------+----------+

| 姓名 | 所属单位 |

+------+----------+

| 朱 | 开发本部 |

| 牛 | 人事部 |

| 马 | 开发本部 |

+------+----------+

*/

/*********************************问题 2 **************************************/

drop table if exists s

create table if not exists s ( sno varchar(32), sname varchar(32))

insert into s values ('s1', '朱')

insert into s values ('s2', '牛')

insert into s values ('s3', '杨')

insert into s values ('s4', '马')

insert into s values ('s5', '吕')

insert into s values ('s6', '于')

insert into s values ('s7', '侯')

drop table if exists c

create table if not exists c ( cno varchar(32), cname varchar(32),

cteacher varchar(32))

insert into c values ('c1', '数学', '张')

insert into c values ('c2', '日语', '李')/*假设李老师同时教授日语和英语*/

insert into c values ('c3', '英语', '李')

drop table if exists sc

create table if not exists sc (sno varchar(32), cno varchar(32),

scgrade double)

insert into sc values ('s1', 'c1', 75)

insert into sc values ('s1', 'c2', 70)

insert into sc values ('s1', 'c3', 80)

insert into sc values ('s2', 'c1', 50)

insert into sc values ('s2', 'c3', 40)

insert into sc values ('s3', 'c1', 50)

insert into sc values ('s3', 'c2', 60)

insert into sc values ('s4', 'c1', 90)

insert into sc values ('s4', 'c2', 40)

insert into sc values ('s4', 'c3', 20)

insert into sc values ('s5', 'c1', 80)

insert into sc values ('s6', 'c1', 85)

/* 1. 没有 选 修过“李”老师讲授课程的所有学生姓名 */

select s.sname '姓名' from s where s.sno not in

(select sc.sno from sc where sc.cno in

(select c.cno from c where c.cteacher = '李'))

/* 2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩 */

select s.sname '姓名', AVG(sc.scgrade) '平均成绩' from s, sc

where s.sno = sc.sno

and

(select count(sc.sno) from sc where sc.sno = s.sno

and sc.scgrade <60 ) >= 2

group by s.sno

/* 3. 列出既学过“C1”号课程，又学过“C2”号课程的所有学生姓名 */

select s.sname '姓名' from s where s.sno in

(select t1.sno from sc t1, sc t2

where t1.sno = t2.sno and t1.cno = 'c1' and t2.cno = 'c2')

/*或者*/

select s.sname '姓名' from s where s.sno in

(select sc.sno from sc where sc.cno = 'c1' and sc.sno in

(select t1.sno from sc t1 where t1.cno = 'c2'))

/* 4. 列出“C1”号课成绩比“C2”号同学该门课成绩高的所有学生的学号 */

select t1.sno '学号' from sc t1, sc t2

where t1.sno = t2.sno and t1.cno = 'c1'

and t2.cno = 'c2' and t1.scgrade >t2.scgrade

/* 5. 列出“C1”成绩比“C2”成绩高的学生的学号及其“C1”和“C2”的成绩 */

select t1.sno '学号', t1.scgrade 'C1成绩', t2.scgrade 'C2成绩' from sc t1, sc t2

where t1.sno = t2.sno and t1.cno = 'c1'

and t2.cno = 'c2' and t1.scgrade >t2.scgrade

/* 运行结果

------------------------------------1

+------+

| 姓名 |

+------+

| 吕 |

| 于 |

| 侯 |

+------+

------------------------------------2

+------+----------+

| 姓名 | 平均成绩 |

+------+----------+

| 牛 | 45 |

| 马 | 50 |

+------+----------+

------------------------------------3

+------+

| 姓名 |

+------+

| 朱 |

| 杨 |

| 马 |

+------+

------------------------------------4

+------+

| 学号 |

+------+

| s1 |

| s4 |

+------+

------------------------------------5

+------+--------+--------+

| 学号 | C1成绩 | C2成绩 |

+------+--------+--------+

| s1 | 75 | 70 |

| s4 | 90 | 40 |

+------+--------+--------+

*/

---