當前位置:首頁 » 編程語言 » sql裡面的choose
擴展閱讀
webinf下怎麼引入js 2023-08-31 21:54:13
堡壘機怎麼打開web 2023-08-31 21:54:11

sql裡面的choose

發布時間: 2022-08-20 02:35:32

❶ 誰能用sql語句檢索下 在testdb2資料庫的choose2表中檢索考試超過三門(包括3門)的男生個數。

select count(*)
from (select distinct 學號 from choose2 group by 學號 having count(課程)>=3)) t1
join 學生表 t2 on t1.學號=t2.學號
where t2.性別='男'

❷ plsql中all rows,rule,first rows,Choose有何區別及相應的功能是什麼oracle中10g,11g

除了rule是關鍵字之外 ,其他三個 不能再PL/SQL中使用 ,沒有什麼功能!

❸ 「sqlserver」三表聯如何查詢「sql」語句

假設學生表叫student,課程表叫class,選課表叫choose
1.三層嵌套的問題
select student.name from student where student.id IN
(select choose.sid from choose where choose.cid NOT IN
(select class.id from class where class.teacher='李明'))
2.一個內連接,一個嵌套
select student.name,avg(choose.score) from
student inner join choose on student.id=choose.sid
where student.id IN
(select choose.sid from choose
where choose.score<'60'
group by choose.sid
having count(choose.sid)>=2)
gruop by student.id
3.一個聯合查詢,一個嵌套查詢
select student.name from student
where student.id IN
(select c1.sid from choose c1 where choose.cid='1'
union
select c2.sid from choose c2 where choose.cid='2'
on c1.sid=c2.sid
)
4.其實就是自連接查詢和行列交換的問題:
select student.id,
(case choose.id when '1' then choose.score end) as 1號課成績,
(case choose.id when '2' then choose.score end) as 2號課成績,
from student inner join choose on student.id=choose.sid sc1,
student inner join choose on student.id=choose.sid sc2
where sc1.id='1'
and sc2.id='2'
and sc1.score>sc2.score

❹ 動態sql if 和choose有什麼意思

1.精簡mybaties工程如下

2.StudentMapper 新建兩個查詢介面,傳入參數都是Map

3.StudentMapper.xml配置如下,
代碼解讀:入參Map如果包含stuName的話,就新增一段查詢條件,如果包含age的話,就再增一段
解決多參數,不固定查詢條件
4.測試查詢方法
5.StudentMapper.xml配置方法2如下
代碼解讀:
當入參searchBy為gradeId 或者stuName的時候,對應的查詢條件,否則還有默認按age查詢
解決參數固定,條件不定的情況
6.測試方法如下
7.註:chooese也可以換成if條件來編寫,看個人編碼習慣即可

❺ 動態實現sql查詢條件替換

<select id="queryEmp" resultType="cn.test.entity.Emp">
select * from emp where 1=1
<if test="deptNo!=null">
and deptno=#{deptNO}
</if>
<if test="deptName!=null">
and deptno=#{deptName}
</if>
</select>

註:<if test="deptNo!=null">中 的deptNo是指實體類中的屬性或欄位;
choose:
<select id="queryEmp" resultType="cn.test.entity.Emp">
select * from emp where 1=1
<choose>
<when test="deptNo!=null">
and deptno=#{deptNo}
</when>
<when test="deptName!=null">
and deptname=#{deptName}
</when>
<otherwise>
and personnum>#{personNum}
</otherwise>
</choose>
</select>

❻ SQL資料庫高手來~~懸賞

--1.新建一個名為school的資料庫。
create database school
go
use school
go
create table student--學生信息表(student)
(
stu_no varchar(50) primary key,
stu_name varchar(100) ,
stu_sex varchar(2),
stu_phone varchar(200),
stu_email varchar(500)
)
go
insert into student values('001','張三','男','111','[email protected]')
insert into student values('002','李四','男','222','[email protected]')
insert into student values('003','王五','女','333','[email protected]')
insert into student values('004','趙六','女','444','[email protected]')
insert into student values('005','錢七','男','555','[email protected]')
go
create table teacher
(
tea_no varchar(50) primary key,
tea_name varchar(100),
tea_sex varchar(2),
tea_phone varchar(200),
tea_email varchar(500)
)
go
insert into teacher values('001','馮老師','女','123','[email protected]')
insert into teacher values('002','劉老師','男','321','[email protected]')
go
create table course--課程表(course)
(
cor_id int primary key,
cor_name varchar(100),
tea_no varchar(50) foreign key references teacher(tea_no)
)
go
insert into course values(991,'資料庫','001')
insert into course values(992,'操作系統','002')
go
create table choose--選課表(choose)
(
ch_id int identity primary key,
stu_no varchar(50) foreign key references student(stu_no),
cor_id int foreign key references course(cor_id)
)
go
insert into choose values('001',991)
insert into choose values('001',992)
insert into choose values('002',991)
insert into choose values('002',992)
insert into choose values('003',991)
insert into choose values('004',992)
go

--1.選出教師信息表(teacher)所有數據。(10分)

select * from teacher

--2.選出前三條的學生信息數據。(10分)

select top 3* from student order by stu_no

--3.選出張三同學選修的所有課程名稱。(10分)

select cor_name as '張三同學選修的所有課程名稱' from course where cor_id in (select cor_id from choose where stu_no=(select stu_no from student where stu_name = '張三'))

--4.選出沒有選課的學生。(10分)

select * from student where stu_no not in(select stu_no from choose)

--5.選出每個學生及他們選課的門數。(10分)

select student.stu_no,student.stu_name,student.stu_sex,student.stu_phone,student.stu_email,isnull(coun,0) from student left join (select stu_no,count(*)as coun from choose group by stu_no) as nes on student.stu_no = nes.stu_no

select stu_no,count(*) from choose group by stu_no
--6.選出馮老師所教的所有學生。(10分)
select * from student where stu_no in(select stu_no from choose where cor_id in(select cor_id from course where tea_no=(select tea_no from teacher where tea_name = '馮老師')))

可以復制到資料庫 運行

❼ mybatis中動態sql語句有哪些

mybatis的動態sql是基於ognl表達式的,它可以幫助我們方便的在sql語句中實現某些邏輯。
mybatis中用於實現動態sql的元素主要有:
if
choose(when,otherwise)
trim
where
set
foreach
http://www.open-open.com/lib/view/open1326100765125.html
詳細可以參考這個,有圖解教程,希望可以幫到你

❽ 三表聯查的SQL語句

這問題交給我吧,假設學生表叫student,課程表叫class,選課表叫choose
1.三層嵌套的問題
select student.name from student where student.id IN
(select choose.sid from choose where choose.cid NOT IN
(select class.id from class where class.teacher='李明'))
2.一個內連接,一個嵌套
select student.name,avg(choose.score) from
student inner join choose on student.id=choose.sid
where student.id IN
(select choose.sid from choose
where choose.score<'60'
group by choose.sid
having count(choose.sid)>=2)
gruop by student.id
3.一個聯合查詢,一個嵌套查詢
select student.name from student
where student.id IN
(select c1.sid from choose c1 where choose.cid='1'
union
select c2.sid from choose c2 where choose.cid='2'
on c1.sid=c2.sid
)
4.好吧,看起來很難,其實就是自連接查詢和行列交換的問題
select student.id,
(case choose.id when '1' then choose.score end) as 1號課成績,
(case choose.id when '2' then choose.score end) as 2號課成績,
from student inner join choose on student.id=choose.sid sc1,
student inner join choose on student.id=choose.sid sc2
where sc1.id='1'
and sc2.id='2'
and sc1.score>sc2.score

5.至於你說的insert報錯的問題,我想可能是因為學生ID和課程ID這兩個外鍵有重復的值,
你可以檢查下,實在不行刪除外鍵,插入數據,在這里外鍵對你最後要的結果影響不大。

純手工打造~有幫助記得給分哈

❾ sqlserver三表聯查sql語句

假設學生表叫student,課程表叫class,選課表叫choose
1.三層嵌套的問題
select student.name from student where student.id IN
(select choose.sid from choose where choose.cid NOT IN
(select class.id from class where class.teacher='李明'))
2.一個內連接,一個嵌套
select student.name,avg(choose.score) from
student inner join choose on student.id=choose.sid
where student.id IN
(select choose.sid from choose
where choose.score<'60'
group by choose.sid
having count(choose.sid)>=2)
gruop by student.id
3.一個聯合查詢,一個嵌套查詢
select student.name from student
where student.id IN
(select c1.sid from choose c1 where choose.cid='1'
union
select c2.sid from choose c2 where choose.cid='2'
on c1.sid=c2.sid
)
4.其實就是自連接查詢和行列交換的問題:
select student.id,
(case choose.id when '1' then choose.score end) as 1號課成績,
(case choose.id when '2' then choose.score end) as 2號課成績,
from student inner join choose on student.id=choose.sid sc1,
student inner join choose on student.id=choose.sid sc2
where sc1.id='1'
and sc2.id='2'
and sc1.score>sc2.score