select a.volumeName, a.checkTime,a.naturalNumber,a.readNumber,b.errorName from volume_info a,error_type b
where a.volumeName = '2008102102' and a.errorType = b.errorType and a.errorType in
(select errorType from error_type where flag = '1')
select a.volumeName, a.checkCause,a.errorType from volume_info a,error_type b
where a.volumeName = '2008102307' and a.errorType = b.errorType and a.errorType in
(select error_type.errorType from error_type where flag = '1')
select a.productno,a.paramCD,b.ctrlid,c.ID,c.type from m_param as a,m_param_ctrl as b,CtrlInfo as c
where a.productno = 'H12020024'
and a.paramCD = 'cpph'
and a.productno = b.productno
and a.no = b.paramID
and b.ctrlid = c.ID
order by c.order
//查询表中满足条件的后十条记录
select top 10 * from volume_info where volumeName = '2008102401' order by id desc
#########################################################################
#########################################################################
已经知道原表 t_salary
year salary
------------------ ---------------------
2000 1000
2001 2000
2002 3000
2003 4000
显示查询结果
year salary
------------------ ---------------------
2000 1000
2001 3000
2002 6000
2003 10000
即salary为以前年的工资的和;
解法1:
select a.year,(select sum(b.salary) from t_salary b where b.year<=a.year) as sum from t_salary a group by year
解法2:
select a.year,(select sum(a.salary) from t_salary b where b.year<=a.year) as sum from t_salary a
解法3:
select b.year,sum(a.salary) from t_salary a,t_salary b where a.year<=b.year group by b.year order by b.year
###############################################################################
###############################################################################
一道凊华同方的SQL面试题(请高手帮忙解决)
已知一个表的结构为:
姓名 科目 成绩
张三 语文 20
张三 数学 30
张三 英语 50
李四 语文 70
李四 数学 60
李四 英语 90
怎样通过select语句把他变成以下结构:
姓名 语文 数学 英语
张三 20 30 50
李四 70 60 90
解答:
select a.name,a.core,b.core,c.core from t_student a,t_student b,t_student c
where a.name = b.name and a.name = c.name and a.subject = '语文' and b.subject = '数学' and c.subject = '英语'
############################################################################
############################################################################
sql面试题一条语句查询每个部门共有多少人
悬赏分:15 - 解决时间:2007-3-22 13:31
前提:a 部门表 b 员工表
a表字段(
id --部门编号
departmentName-部门名称
)
b表字段(
id--部门编号
employee- 员工名称
)
问题:如何一条sql语句查询出每个部门共有多少人
解答1(嵌套查询):
SELECT a.dpid AS dpid, (select count(b.employee) as sum from t_epinfo b where b.dpid = a.dpid ) AS emplyoeecount
FROM t_dpinfo AS a
解答2(左联查询).
select a.dpname,count(b.employee)as employeecount from t_dpinfo as a left join t_epinfo as b on a.dpid=b.dpid
group by a.dpname
解答3.
select a.dpname,count(b.employee) as epcount from t_dpinfo a,t_epinfo b where a.dpid = b.dpid group by a.dpname
#########################################################################