ORDER BY - и параллельные столбцы с одинаковыми именами

Question

Привет всем =)

 

Наткнулся на такой момент и не пойму как вырулить...

Есть таблицы A,B,C,D,E (у них длина одинаковая)

В каждой из них есть столбцы name, last_name, home.

На строку если в столбце name одной таблицы например А есть значение, то в остальных строках В,С,D,E параллельно name=NULL

А мне стало интересно, как сделать запрос на выборку с возможностью применения 3-х параметров

 

CREATE PROCEDURE procedure1 

@name varchar(100), @last_name varchar(100), @home varchar(100)

BEGIN

END

Ну это понятно... А вот самое интересное

параметры @name, @last_name и @home могут быть равны как NULL так и иметь значения.

параметры могут иметь значения как все так и по отдельности.

И здесь я не совсем понимаю...

Таблиц 5 шт, нужно определить где есть значения, а где NULL на строку и потом отсортировать через ORDER BY в соответствии с входящими параметрами, которые имеют значения.

 

CREATE PROCEDURE procedure1 

@name varchar(100), @last_name varchar(100), @home varchar(100) 

BEGIN

SELECT a.name, a.last_name, a.home,

            b.name, b.last_name, b.home,

            c.name, c.last_name, c.home,

            d.name, d.last_name, d.home,

            e.name, e.last_name, e.home

FROM A AS a

INNER JOIN B AS b

ON a.id=b.id

INNER JOIN C AS c

ON b.id=c.id

INNER JOIN D AS d

ON c.id=d.id

INNER JOIN E AS e

ON d.id=e.id

/*

WHERE - Здесь входящие параметры могут содержать NULL значения, потому такой способ меня смущает. хотелось бы узнать ваше мнение...

*/

WHERE x.name=@name AND x.last_name=@last_name AND x.home=@home

/*

ORDER BY - тут по алфавиту хочется вывести, но опять же переменные задавать вероятно нельзя, а как приспособить имена столбцов при том, что нужно избежать NULL значений? Это так же хотелось бы послушать ваше мнение.

*/

ORDER BY @name, @last_name, @home DESC

END

 

 

под х я имею ввиду, что заранее не знаю какая таблица будет попадать под ненулевые значения на столбец... И как раз тут загвоздка.

 

Чтобы было проще понять, я примерно опишу все 5 таблиц и тот результат, что хочу получить...

-------------А--------------|------------B-------------|------------C-------------|

name | last_name| home|name|last_name|home|name|last_name|home|

John   |Smith       | MZ      |NULL|NULL         |NULL |NULL | NULL       |NULL |

NULL  |NULL         |NULL  |Any | Watson       |CA    |NULL |NULL        |NULL |

NULL  |NULL         |NULL  |NULL|NULL           |NULL |Bob   | Mills        |NY    |

NULL  |NULL         |NULL  |John  |Smith         |NY     |NULL |NULL        |         |

=================================================

получаем параметры: 

@name='John'

@last_name=NULL

@home=NULL

получить результат

John Smith MZ

John Smith NY

- или -

@name=NULL

@last_name=NULL

@home='NY'

результат:

Bob Mills NY

John Smith NY

 

Помогите понять 

 

 

 

 

 

Answer 1

WHERE - Здесь входящие параметры могут содержать NULL значения, потому такой способ меня смущает. хотелось бы узнать ваше мнение...

*/

WHERE x.name=@name AND x.last_name=@last_name AND x.home=@home

/*

...WHERE ( @name is null ) or ( x.name = @name ) и т.д. 

/*

ORDER BY - тут по алфавиту хочется вывести, но опять же переменные задавать вероятно нельзя, а как приспособить имена столбцов при том, что нужно избежать NULL значений? Это так же хотелось бы послушать ваше мнение.

*/

ORDER BY @name, @last_name, @home DESC

... ORDER BY case when @name is not null then name else null end и т.д.

---

http://www.t-sql.ru

Answer 2

Я немного добавил текст... Добавил ещё строку и какой результат предполагаю.

Посмотрите пожалуйста.

Насчет case, то я думал об этом, но как быть если она даст NULL? Сортировка по пустому значению позволяется? 

Answer 3

Но как быть с метками? ORDER BY для a.name или b.name или c.name итд?

Проблема как раз в том, чтобы определить актуальность метки для последующей сортировки уже по x.name например. Или можно найти обходной способ? 

Answer 4

Я думаю, надо поступить так:

create table #Result (FirstName varchar(100),LastName varchar(100), Address varchar(500))

IF @Name IS NOT NULL and @LastName IS NOT NULL and @Home IS NOT NULL

  begin

   insert into #Result select Name, LastName, Home from Table1 where Name = @Name and LastName = @LastName and Home = @Home

   same insert for other 4 tables

  end

ELSE

  IF @Name IS NOT NULL and @LastName IS NULL and @Home IS NOT NULL

     etc. - consider all possible cases

 

Alternative solution - dynamic SQL:

Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later)

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 5

use tempdb
go

create table a(name varchar(50), last_name varchar(50), home varchar(50))
go
create table b(name varchar(50), last_name varchar(50), home varchar(50))
go
create table c(name varchar(50), last_name varchar(50), home varchar(50))
go
create table d(name varchar(50), last_name varchar(50), home varchar(50))
go
create table e(name varchar(50), last_name varchar(50), home varchar(50))
go

insert into a(name,last_name,home)values('John','Smith','MZ')
go
insert into b(name,last_name,home)values('Any','Watson','CA')
go
insert into b(name,last_name,home)values('John','Smith','NY')
go
insert into c(name,last_name,home)values('Bob','Mills','NY')
go


create proc get_data
	@name varchar(50),
	@last_name varchar(50),
	@home varchar(50)
as
begin

	;with x as (
		select name,last_name,home from a
		union all
		select name,last_name,home from b
		union all
		select name,last_name,home from c
		union all
		select name,last_name,home from d
		union all
		select name,last_name,home from e
	)
	select
		name,
		last_name,
		home 
	from
		x
	where
		(name = @name or @name is null)
		and (last_name = @last_name or @last_name is null)
		and (home = @home or @home is null)
end
go

declare @name varchar(50)= 'John'
declare @last_name varchar(50) = NULL
declare @home varchar(50) = NULL

exec get_data @name, @last_name,@home
go
--result
--name	last_name	home
--John	Smith	MZ
--John	Smith	NY

declare @name varchar(50) = NULL
declare @last_name varchar(50) = NULL
declare @home varchar(50) = 'NY'
exec get_data @name, @last_name,@home
go
--result
--name	last_name	home
--John	Smith	NY
--Bob	Mills	NY

---

Gruß Yury

Answer 6

Я думаю, надо поступить так:

create table #Result (FirstName varchar(100),LastName varchar(100), Address varchar(500))

IF @Name IS NOT NULL and @LastName IS NOT NULL and @Home IS NOT NULL

  begin

   insert into #Result select Name, LastName, Home from Table1 where Name = @Name and LastName = @LastName and Home = @Home

   same insert for other 4 tables

  end

ELSE

  IF @Name IS NOT NULL and @LastName IS NULL and @Home IS NOT NULL

     etc. - consider all possible cases

 

Alternative solution - dynamic SQL:

Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later)

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Здорово со временными таблицами вариант :) А вот с пересчетом всех вариантов - это можно как-то оптимизировать, чтобы поменьше ветвлений было?

Answer 7

WHERE - Здесь входящие параметры могут содержать NULL значения, потому такой способ меня смущает. хотелось бы узнать ваше мнение...

*/

WHERE x.name=@name AND x.last_name=@last_name AND x.home=@home

/*

...WHERE ( @name is null ) or ( x.name = @name ) и т.д. 

/*

ORDER BY - тут по алфавиту хочется вывести, но опять же переменные задавать вероятно нельзя, а как приспособить имена столбцов при том, что нужно избежать NULL значений? Это так же хотелось бы послушать ваше мнение.

*/

ORDER BY @name, @last_name, @home DESC

... ORDER BY case when @name is not null then name else null end и т.д.

---

http://www.t-sql.ru

А в таком  варианте можно по 2 например имени столбца указывать?

Типа:

ORDER BY case when @name is not null then name,last_name else null end 

 

 

?

Answer 8

Как вариант - создавать dynamic search string and then execute with sp_ExecuteSQL

Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 9

Здорово, Юрий - спасибо :)

Но думаю в моем описании не хватает некоторой детали для более полной картины. 

Дело в том, что в таблице А не 3 столбца, но много и содержит она общие данные для любого сотрудника например так...

-------------------------A---------------------------

id |employee | name  | last_name| home | address | telephone      | salary | points | merried|  

1  |artist       |Holly    |Nails          | CA     | Costy st | X-XXX-XXX...| 3.000  | 25      | no       | 

2  |manager  |John    |Smith        | NY     | Rose st  | X-XXX-XXX...| 1.500  | 12      | yes      |

3  |manager  |Peter   |Snow         | CA    | Riddle st | X-XXX-XXX...| 2.500  | 20      | no       | 

4  |driver      |Mike    |Born          | NY     | Maple st | X-XXX-XXX...| 1.000  | 10      | no       | 

5  |driver      |Josh    |Willard       | CA      | Holly st | X-XXX-XXX...| 1.500  | 9        | yes      | 

6  |artist       |Emma |Beyond       | MZ     | Berry st | X-XXX-XXX...| 3.500  | 30      | yes      | 

Таблицы В, С, D, E же содержат параллельно особенные (уточняющие) данные по каждому сотруднику, например как

--------------------------B (driver)---------------------------

 

id| driveing_license|   driving_period_years | total_hours

1 |     NULL            | NULL                          |NULL

2 |     NULL            | NULL                          |NULL

3 |     NULL            | NULL                          |NULL

4 |     B                 | 5                                   | 8200

5 |     A                 | 3                                   | 7400

6 |     NULL            | NULL                          |NULL

 

--------------------------C (artist)---------------------------

id| number_of_works| current_order_status|total_hours

 

1 |     20                   | engaged                      |12000

2 |     NULL               | NULL                          |NULL

3 |     NULL               | NULL                          |NULL

4 |     NULL               | NULL                          |NULL

5 |     NULL               | NULL                          |NULL

6 |     18                   | engaged                      |9000

 

Входящие параметры:

@employee=artist

@name =NULL

@last_name = NULL

@total_hours = 10000

/*

Сам запрос имеет ввиду, что WHERE total_hour >=10000

*/

Результат

Holly    |Nails | CA | Costy st | X-XXX-XXX...| 3.000  | 25|  no | 20 | engaged     |12000

   

 

 

Answer 10

use tempdb
go

create table a(id int,employee varchar(255),name varchar(50),last_name varchar(50),home varchar(50),
	address varchar(255),telephone varchar(255),salary decimal(19,4),points int,merried varchar(55))
go
create table b(id int,employee varchar(255),name varchar(50), last_name varchar(50), home varchar(50),
	address varchar(255),telephone varchar(255),salary decimal(19,4),points int,merried varchar(55),
	driveing_license varchar(55),driving_period_years int,total_hours int)
go
create table c(id int,employee varchar(255),name varchar(50), last_name varchar(50), home varchar(50),
	address varchar(255),telephone varchar(255),salary decimal(19,4),points int,merried varchar(55),
	number_of_works int,current_order_status varchar(255),total_hours int)
go
create table d(id int,employee varchar(255),name varchar(50), last_name varchar(50), home varchar(50),
	address varchar(255),telephone varchar(255),salary decimal(19,4),points int,merried varchar(55))
go
create table e(id int,employee varchar(255),name varchar(50), last_name varchar(50), home varchar(50),
	address varchar(255),telephone varchar(255),salary decimal(19,4),points int,merried varchar(55))
go

insert into a (id,employee,name,last_name,home,address,telephone,salary,points,merried)values
(1,'artist','Holly','Nails','CA','Costy st','X-XXX-XXX...',3.000,25,'no')
go
insert into b (id,employee,name,last_name,home,address,telephone,salary,points,merried)values
(1,'artist','Holly','Nails','CA','Costy st','X-XXX-XXX...',3.000,25,'no')
go
insert into c (id,employee,name,last_name,home,address,telephone,salary,points,merried,number_of_works,
	current_order_status,total_hours)values
(1,'artist','Holly','Nails','CA','Costy st','X-XXX-XXX...',3.000,25,'no',20,'engaged',12000)
go
insert into d(id,employee,name,last_name,home,address,telephone,salary,points,merried)values
(1,'artist','Holly','Nails','CA','Costy st','X-XXX-XXX...',3.000,25,'no')
go
insert into e (id,employee,name,last_name,home,address,telephone,salary,points,merried)values
(1,'artist','Holly','Nails','CA','Costy st','X-XXX-XXX...',3.000,25,'no')
go


create proc get_data
	@employee varchar(255),
	@name varchar(50),
	@last_name varchar(50),
	@total_hours int
as
begin
	declare @where nvarchar(max) = ' where 1=1'

	if @employee is not null
	set @where += ' and c.employee = @employee'

	if @name is not null
	set @where += ' and x.name = @name'

	if @last_name is not null
	set @where += ' and x.last_name = @last_name'

	if @total_hours is not null
	set @where += ' and c.total_hours >= @total_hours'

	declare @stmt nvarchar(max)
	set @stmt =
	';with x as (
		select id, name,last_name,home,address,telephone,salary,points,merried from a
		union
		select id, name,last_name,home,address,telephone,salary,points,merried from b
		union
		select id, name,last_name,home,address,telephone,salary,points,merried from c
		union
		select id, name,last_name,home,address,telephone,salary,points,merried from d
		union
		select id, name,last_name,home,address,telephone,salary,points,merried from d
	)
	select
		x.id,
		x.name,x.last_name,x.home,
		x.address,x.telephone,
		x.salary,x.points,x.merried,
		c.current_order_status,
		c.total_hours
	from
		x inner join b on x.id = b.id
		inner join c on x.id = c.id
		inner join d on x.id = d.id
		inner join e on x.id = e.id' + @where

	declare @params nvarchar(max) = '@employee varchar(255),@name varchar(50),@last_name varchar(50),@total_hours int'
	exec sp_executesql @stmt,@params,@employee,@name,@last_name,@total_hours
end
go

declare @employee varchar(255) = 'artist'
declare @name varchar(50)= NULL
declare @last_name varchar(50) = NULL
declare @total_hours int = 10000

exec get_data @employee, @name, @last_name, @total_hours
go
--------------------
-- result
--id	name	last_name	home	address	telephone	salary	points	merried	current_order_status	total_hours
--1	Holly	Nails	CA	Costy st	X-XXX-XXX...	3.0000	25	no	engaged	12000

---

Gruß Yury

Answer 11

ORDER BY case when @Name is NOT NULL then Name end, case when @Name IS NOT NULL then LastName end

 

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

А это какой синтаксической записи будет соответствовать в классическом виде если true?

ORDER BY name, LastName

?

 

Answer 12

Да, здорово, Юрий :) С динамичным запросом интересно получается. Я как-то их старался не использовать, чтобы через утилиту не пропускать строку лишний раз... Но пример интересный - спасибо буду разбирать. 

Я просто думал, что раз строки таблиц параллельны, то можно через INNER JOIN их совместить для запроса, указать выражения WHERE значения для profession и total_hours, затем выбрать те строки, в которых нет NULL значений :*) 

 

А что если явно не указывать название столбца через метку? то есть применить INNER JOIN к таблицам, у которых есть столбцы с одинаковыми именами? Например есть 2 таблицы:

----------------------------A--------------------------

id| number_of_works| current_order_status|total_hours

 

----------------------------B--------------------------

id| number_of_works| current_order_status|total_hours

 

делаем запрос:

 

SELECT id, number_of_works, current_order_status,total_hours

FROM A AS a

INNER JOIN B as b

ON a.id=b.id

 

?

 

 

 

 

 

 

 

Answer 13

Esli @Name IS NOT NULL to

ORDER BY Name, LastName

Esli @Name IS NULL

to proizvol'nyj ORDER.

 

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Спасибо - интересно =)

мой последний пост...

SELECT id, number_of_works, current_order_status,total_hours

FROM A AS a

INNER JOIN B as b

ON a.id=b.id

Я не совсем понимаю будут ли дублироваться столбцы из двух таблиц или нет при запросе к таблицам с одинаковыми именами столбцов? 

Answer 14

Esli @Name IS NOT NULL to

ORDER BY Name, LastName

Esli @Name IS NULL

to proizvol'nyj ORDER.

 

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

И ещё... Я не совсем понял этот пример, а точнее как стоят запятые итп

http://msdn.microsoft.com/ru-ru/library/ms181765.aspx

 

SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
 ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
GO

почему после END стоит DESC? потом запятая и уже после конец выражения через ";" ?

Не пойму как сделать сортировку через ветвление, чтобы соответствовало этой записи...

ORDER BY name, last_name, home DESC

ORDER BY name DESC

ORDER BY last_name DESC

ORDER BY home DESC

ORDER BY name,last_name DESC

итд...

... при значении ИСТИНА во всех трех случаях все вроде бы понятно, но проблема в том, что порядок может чередоваться, так как все переменные могут как одновременно так и по отдельности иметь значение NULL. Не совсем пойму можно ли производить сортировку если в одном из выражений вернет false?

Вот написал примерно, но не уверен...

ORDER BY
CASE
WHEN @name IS NOT NULL
THEN name END DESC,
CASE 
WHEN @last_name IS NOT NULL
THEN last_name END DESC,
CASE
WHEN @home IS NOT NULL
THEN home END DESC;

Я исхожу из примера, но верно ли я интерпретировал выражения для своего случая? Интересно послушать ваше мнение.

 

Answer 15

Esli nam tol'ko HOME nado po ybyvaniju, to togda

ORDER BY
CASE
WHEN @name IS NOT NULL
THEN name END,-- no need for DESC
CASE 
WHEN @last_name IS NOT NULL
THEN last_name END, -- no need for DESC
CASE
WHEN @home IS NOT NULL
THEN home END DESC; -- if we want descending order by Home

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 16

Esli nam tol'ko HOME nado po ybyvaniju, to togda

 

ORDER BY
CASE
WHEN @name IS NOT NULL
THEN name END,-- no need for DESC
CASE 
WHEN @last_name IS NOT NULL
THEN last_name END, -- no need for DESC
CASE
WHEN @home IS NOT NULL
THEN home END DESC; -- if we want descending order by Home

 

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

В том штука что заранее не знаю что именно надо :) Может home, а может name и last_name а может только last_name 

Не пойму как быть с запятыми при возврате false для двух булевых выражений? Не выйдет ли такая штука, как:

 

ORDER BY name,(пусто),(пусто) DESC

 

?

Answer 17

Every case пишите отдельно, тогда все будет работать. Как я написала выше.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

А как же быть с запятыми, которые находятся вне case выражения?

ORDER BY ... , ... , ... 

?

Answer 18

ORDER BY FirstField, SecondField, ThirdField where

FirstField can be an expression.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 19

ORDER BY FirstField, SecondField, ThirdField where

FirstField can be an expression.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Не совсем так. Нужно все 3 варьировать :( Не один только в смысле в списке ORDER BY

потому и думаю куда запятые денутся если false вернет в одном или двух из параметров? Запятые ведь не являются частью выражений case, чтобы они так же исчезли если выражение вернет false ) или я не совсем так понял здесь?

Answer 20

Не совсем так. Нужно все 3 варьировать :( Не один только в смысле в списке ORDER BY

потому и думаю куда запятые денутся если false вернет в одном или двух из параметров? Запятые ведь не являются частью выражений case, чтобы они так же исчезли если выражение вернет false ) или я не совсем так понял здесь?

Что-то у вас совсем пробелы в знаниях синтаксиса T-SQL, возьмите книгу для начинающих, тогда 90% вопросов отпадут сами. А то вы только создаете себе проблемы, а решать так и  не пытаетесь

---

http://www.t-sql.ru

Answer 21

Уважаемый andrewmorozov, пожалуйста, пометьте ответы, которые вам помогли в решении проблемы

---

Для связи [mail]