如何使用sql为两个不同的语句获取单个输出 [英] How to get a single output for two different statements using sql

问题描述

这是我的输出格式：

-------------------------------- -------------------------------------------------- -----

CLASS 1A

学生总人数52

学生购买26

留学生26





1）这里''student_table''包含学生ID和班级。

2）'' purchase_table''包含那些购买礼服的学生ID。



现在我想要以上述格式的结果（有多少学生属于'1A'班'和购买的学生的数量，而不是购买的学生）。



i使用这个查询，但我只是没有购买学生的结果。


从student_table中选择count（studentid），其中studentid不存在（从''purchase_table'中选择studentid'）





任何人都有助于解决这个问题。

Hi this is my output format:
---------------------------------------------------------------------------------------
CLASS 1A
TOTAL NO OF STUDENTS 52
STUDENTS PURCHASED 26
REMAINING STUDENTS 26


1)Here ''student_table'' contain student id and class.
2) ''purchase_table'' contain student id those who are purchase the dress.

now i want the result in above format (how many students are belongs to Class ''1A'' and count of purchased students and not purchased students).

i use this query but i get only not purchase students result.

select count(studentid) from student_table where studentid not exists (select studentid from ''purchase_table'')


anybody help to solve this problem.

解决方案

你没有提供表格结构，但提示，...

you have not provide table structure but for hint,...

Select Class_Nm, Sum(TotalStudents) TotalStudents, sum(Pur_students) Pur_students,Sum(TotalStudents)-sum(Pur_students) as Not_Pur_Student
From
(
    select Class_Nm,sum(Student_Id) as TotalStudents,0 as Pur_students 
    from student_table 
    where Class_Nm='1-A' group by Class_Nm
    
    Union all
    
    select Class_Nm,0 as TotalStudents,sum(Student_Id) as Pur_students 
    from student_table 
    where Class_Nm='1-A' and Student_Id not in (select studentid from purchase_table)
    group by Class_Nm
) as a
group by Class_Nm

快乐编码！

：）

Happy Coding!
:)

SELECT class,
       COUNT(*) total,
       COUNT(p.studentid) purchased,
       COUNT(*) - COUNT(p.studentid) remaining
  FROM student_table s LEFT JOIN
       purchase_table p ON s.studentid = p.studentid
 WHERE s.class = '1A'
GROUP BY class;

工作........感谢所有....

its working........thanks to all....

嗨
检查以下查询...尝试这样。

Hi Check the following Query... try like this.

SELECT CASE WHEN M.DispOrd=1 THEN 'CLASS '+ M.Class 
	    WHEN M.DispOrd=2 THEN 'TOTAL NO OF STUDENTS '+ M.Class + ' ' + CAST(M.Count AS VARCHAR(10))
	    WHEN M.DispOrd=3 THEN 'STUDENTS PURCHASED '+ M.Class + ' ' + CAST(M.Count AS VARCHAR(10))
	ELSE 'REMAINING STUDENTS '+ M.Class + ' ' + CAST(M.Count AS VARCHAR(10)) 
	END 'Result'
FROM (SELECT DISTINCT Class,NULL 'Count, 1 'DispOrd'
     FROM Student_Table
     UNION ALL
     SELECT Class ,COUNT(StudentID), 2
     FROM Student_Table
     GROUP BY Class
     UNION ALL
     SELECT Class, COUNT(StudentID), 3
     FROM Purchase_Table 
     GROUP BY Class
     UNION ALL
     SELECT T.Class,T.Count, 4
     FROM (SELECT S.Class, COUNT(S.StudentID)-COUNT(P.StudentID)
           FROM Student_Table S
       	   LEFT OUTER JOIN Purchase_Table P On P.studentID=S.StudentID
           GROUP BY S.Class) T
     ) M
ORDER BY M.Class, M.DispOrd
-- Note : Check the Syntax bcos I don't have SQL server in my machine :-)

问候

gvprabu

regards
gvprabu

这篇关于如何使用sql为两个不同的语句获取单个输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！