Вопрос:
Я хочу рассчитать мужчин, женщин и учащихся из таблицы Студентов в течение определенного года. Я хочу, чтобы результат можно было отобразить в форме:
==================================== | Label | Value | Year | ==================================== | Male | 0 | 2013 | | Female | 23 | 2013 | | Total | 23 | 2013 | ====================================
Запрос должен отображать 0, если нет совпадений между мужчинами и женщинами в течение указанного года. Любая идея, как я могу это сделать?
Заранее спасибо
Ответ №1
Рассмотрим следующий запрос:
select max(registeredYear) as year, count(case when gender=’Male’ then 1 end) as male_cnt, count(case when gender=’Female’ then 1 end) as female_cnt, count(*) as total_cnt from student where registeredYear = 2013 group by registeredYear;
Результат будет таким:
Year male_cnt female_cnt total_cnt —- ——— ———- ——— 2013 0 23 23
Вы можете преобразовать этот результат в форму, которую вы хотите. Если вы хотите сделать это в запросе, то вы можете сделать это так:
with t as ( select max(registeredYear) as year, count(case when gender=’Male’ then 1 end) as male_cnt, count(case when gender=’Female’ then 1 end) as female_cnt, count(*) as total_cnt from student where registeredYear = 2013 group by registeredYear) select ‘Male’, male_cnt as male, year from t union all select ‘Female’, female_cnt as male, year from t union all select ‘Total’, total_cnt as male, year from t ; Ответ №2
Вы должны использовать:
select name, COUNT(*)as tot, COUNT(case when details.gender=’male’ then 1 end) as male, COUNT(case when details.gender=’female’ then 1 end) as female from details group by name Ответ №3
Поскольку вы не должны смешивать форматирование сетки с извлечением данных
SELECT SUM(CASE WHEN gender = ‘Male’ THEN 1 ELSE 0 END) as MaleCount, SUM(CASE WHEN gender = ‘Female’ THEN 1 ELSE 0 END) as FemaleCount, COUNT(*) as TotalCount FROM student WHERE registeredYear = 2013 Ответ №4
Я считаю, что это примерно так же эффективно, как вы можете получить всего один проход через таблицу учеников. Просто измените год в году CTE по мере необходимости.
with year as ( select ‘2013’ year ), gender as ( select ‘Male’ gender union all select ‘Female’ gender ) select coalesce(g.gender,’Total’) «Label», count(s.gender) «Value», y.year «Year» from gender g cross join year y left join student s on s.gender = g.gender and s.year = y.year group by grouping sets( (g.gender, y.year), (y.year) ) order by case g.gender when ‘Male’ then 1 when ‘Female’ then 2 else 3 end ;
Полностью нормализованная модель данных, вероятно, будет иметь как школьный год, так и гендерную таблицу, поэтому CTE не нужны. (если вы действительно не хотите возвращать строки в течение многих лет, у которых нет данных)
Вот демон демонстраций sqlfiddle без идентификатора и имени ученика, поскольку они посторонние к проблеме.
Ответ №5
Ваш запрос кажется очень простым, но он имеет две сложности. Во-первых, одна строка представляет собой резюме двух других. Это предполагает использование rollup или grouping sets в запросе.
Второе требование – иметь значения, даже если у вас нет данных. Это предполагает использование подзапроса “драйвер”. Такой подзапрос определяет все строки на выходе перед назначением значений. Вы используете таблицу драйверов с left outer join.
Неустановленным требованием может быть упоминание только одного года.
Следующий подход к запросу ставит окончательную форму вместе в течение года. Затем левый соединяет сводку, вытягивая значения оттуда, если таковые имеются:
with year as ( select 2013 as Year ) select driver.label, coalesce(s.value, 0) as Value, driver.Year from ((select ‘Male’ as label, year from year ) union all (select ‘Female’ as label, year from year ) union all (select ‘Total’ as label, year from year ) ) driver left outer join (select coalesce(Gender, ‘Total’) as Gender, year.year, count(*) as value from Students cross join year group by Gender with Rollup ) s on driver.year = s.year;
Это предполагает, что пол представлен как “мужчина” и “женщина”, и что в данных есть столбец с именем year (без ввода образцов или табличных форматов нужно угадывать имена столбцов и значения выборки).
Ответ №6
Просто запустите этот запрос…
SELECT MAX(registeredYear) as Year ,SUM(CASE WHEN gender = ‘Male’ THEN 1 END) AS Male ,SUM(CASE WHEN gender = ‘Female’ THEN 1 END) AS Female ,SUM(CASE WHEN gender IS NOT NULL THEN 1 ELSE 0 END) AS Total FROM from student WHERE registeredYear = 2013 GROUP BY registeredYear; Ответ №7
что-то вроде этого:
select ‘Male’ as Label, count(gender) as Value from student where gender= ‘Male’ union ( select ‘Female’ as Label, count(gender) as Value from student where gender= ‘Female’ ) union ( select ‘Total’ as Label, count(gender) as Value from student ) Ответ №8
Попробуйте это, не допуская нулей в “Пол” или “Зарегистрированный”:
WITH AllYears AS ( SELECT RegisteredYear FROM Student GROUP BY RegisteredYear ) , AllGenders AS ( SELECT Gender FROM Student GROUP BY Gender ) , AllGendersAndYears AS ( SELECT Gender, RegisteredYear FROM AllGenders, AllYears ) SELECT Gender, RegisteredYear, CountForGenderAndYear FROM AllGendersAndYears CROSS APPLY ( SELECT COUNT(*) AS CountForGenderAndYear FROM Student WHERE Student.Gender = AllGendersAndYears.Gender AND Student.RegisteredYear = AllGendersAndYears.RegisteredYear ) countForGenderAndYear UNION ALL SELECT ‘Total’, AllYears.RegisteredYear, CountForYear FROM AllYears CROSS APPLY ( SELECT COUNT(*) AS CountForYear FROM Student WHERE Student.RegisteredYear = AllYears.RegisteredYear ) countForYear Ответ №9
Вот еще одна вариация, использующая UNPIVOT. Это специально ищет только MALE и FEMALE, поэтому он не такой гибкий, как мой другой (так как вам нужно жестко кодировать каждый пол). Но это, вероятно, самый эффективный.
WITH AllYears (RegisteredYear) AS ( —SELECT DISTINCT RegisteredYear —FROM Student —…OR… SELECT 2014 ) , GenderAndYearCounts AS ( SELECT RegisteredYear , SUM(CASE Gender WHEN ‘MALE’ THEN 1 ELSE 0 END) MaleCount , SUM(CASE Gender WHEN ‘FEMALE’ THEN 1 ELSE 0 END) FemaleCount , COUNT(*) YearCount FROM Student GROUP BY RegisteredYear ) , GenderAndYearCountsForAllYears AS ( SELECT AllYears.RegisteredYear , ISNULL(MaleCount, 0) AS MaleCount , ISNULL(FemaleCount, 0) AS FemaleCount , ISNULL(YearCount, 0) AS YearCount FROM AllYears LEFT JOIN GenderAndYearCounts ON GenderAndYearCounts.RegisteredYear = AllYears.RegisteredYear ) SELECT Label, Value, RegisteredYear FROM ( SELECT RegisteredYear, MaleCount AS Male, FemaleCount AS Female, YearCount AS Total FROM GenderAndYearCountsForAllYears ) allCounts UNPIVOT ( Value FOR Label IN (Male, Female, Total) ) unpivotted Ответ №10
Все полы, затем все годы, затем подсчеты:
declare @Year int set @Year = 2014 select labels.label, counts.cnt, @Year as registeredYear from (select ‘Male’ as label, 1 as sortOrder union all select ‘Female’, 2 union all select ‘All’, 3) as labels left join (select gender, count(1) cnt from student where registeredYear = @Year group by gender) as counts on labels.label = counts.gender order by labels.sortOrder Ответ №11
Это сработало для меня. Но все же он не мог отображать 0 для M и F в течение лет, где нет данных:
Select * from ( SELECT isnull (SUM(CASE WHEN gender = ‘M’ THEN 1 ELSE 0 END),0) as Male, isnull(SUM(CASE WHEN gender = ‘F’ THEN 1 ELSE 0 END),0) as Female, registeredYear as ‘year’ FROM student WHERE registeredDate.Year = 2013 //could be a variable group by registeredYear ) as s UNPIVOT ( value FOR gender IN (Male, Female) ) Sub Ответ №12select sp.CLASS_Name , count(*) as total , sum( case when si.STDNT_GENDER = 1 then 1 else 0 end ) as Male , sum( case when si.STDNT_GENDER = 0 then 1 else 0 end ) as Female from SCHOOL_PLANE sp inner join STUDENT_INFO si on sp.CLASS_ID=si.STDNT_CLASS_PLANE_ID group by sp.CLASS_Name ——- select sp.CLASS_Name , count(*) as total , sum( case si.STDNT_GENDER when 1 then 1 else 0 end ) as Male , sum( case si.STDNT_GENDER when 0 then 1 else 0 end ) as Female from SCHOOL_PLANE sp inner join STUDENT_INFO si on sp.CLASS_ID=si.STDNT_CLASS_PLANE_ID group by sp.CLASS_Name ———— select sp.CLASS_Name , count(*) as total , count( case when si.STDNT_GENDER = 1 then 1 end ) as Male , count( case when si.STDNT_GENDER = 0 then 1 end ) as Female from SCHOOL_PLANE sp inner join STUDENT_INFO si on sp.CLASS_ID=si.STDNT_CLASS_PLANE_ID group by sp.CLASS_Name Ответ №13
Выбрать из
(выберите год, количество (*) как общее, сумма (случай, когда пол = ‘M’, затем 1 или 0 конец) в качестве мужчины,
сумма (случай, когда пол = ‘F’, затем 1 или 0 конец) как женщина из mytable
где год = 2013
группа по полу, год)
UNPIVOT (come_component_value для дохода_компонента_типа в (мужчина, женщина, всего) )
