Избиране на запис въз основа на цяло число в поле за масив

Имам база данни с къщи. В записа на базата данни mssql има поле, наречено areaID. Една къща може да бъде в множество области, така че записът може да бъде както следва в базата данни:

+---------+----------------------+-----------+-------------+-------+
| HouseID | AreaID               | HouseType | Description | Title |
+---------+----------------------+-----------+-------------+-------+
| 21      | 17, 32, 53           | B         | data        | data  |
+---------+----------------------+-----------+-------------+-------+
| 23      | 23, 73               | B         | data        | data  |
+---------+----------------------+-----------+-------------+-------+
| 24      | 53, 12, 153, 72, 153 | B         | data        | data  |
+---------+----------------------+-----------+-------------+-------+
| 23      | 23, 53               | B         | data        | data  |
+---------+----------------------+-----------+-------------+-------+

Ако отворя страница, която изисква къщи само в зона 53, как ще я търся. Знам, че в MySQL можете да използвате find_in_SET, но аз използвам Microsoft SQL Server 2005.


sql sql-server tsql sql-server-2005
person neojakey    schedule 06.02.2011    source източник
comment
Опция ли е нормализирането на данните във връзка много към много с областите във втора таблица?   -  person Jaymz    schedule 06.02.2011

Отговори (4)


arrow_upward
5
arrow_downward

Ако форматирането ви е ТОЧНО

N1, N2 (e.g.) one comma and space between each N

След това използвайте тази клауза WHERE

WHERE ', ' + AreaID + ',' LIKE '%, 53,%'

Добавянето на префикса и суфикса прави всяко число, където и да е в списъка, последователно обвито със запетая и суфикс със запетая. В противен случай може да получите фалшиви положителни резултати с 53, появяващи се в част от друго число.

Забележка

  1. Изразът LIKE ще бъде всичко друго, но не и бърз, тъй като винаги ще сканира цялата таблица.
  2. Трябва да помислите за нормализиране на данните в две таблици:

Маси стават

House
+---------+----------------------+----------+
| HouseID | HouseType | Description | Title |
+---------+----------------------+----------+
| 21      | B         | data        | data  |
| 23      | B         | data        | data  |
| 24      | B         | data        | data  |
| 23      | B         | data        | data  |
+---------+----------------------+----------+

HouseArea
+---------+-------
| HouseID | AreaID
+---------+-------
| 21      | 17
| 21      | 32
| 21      | 53
| 23      | 23
| 23      | 73
..etc

След това можете да използвате

select * from house h
where exists (
    select *
    from housearea a
    where h.houseid=a.houseid and a.areaid=53)
person RichardTheKiwi    schedule 06.02.2011
comment
Много благодаря, създаването на втора маса, макар и трудоемко, беше точно това, което трябваше да направя. Много благодаря за съвета... много го оценявам.. - person neojakey; 07.02.2011

arrow_upward
0
arrow_downward

2 опции, променете идентификаторите на AreaId, така че да можете да използвате оператора & ИЛИ създайте таблица, която свързва House и Area....

person Tim Mahy    schedule 06.02.2011
comment
Бихте ли обяснили допълнително използването на оператора &..? - person neojakey; 06.02.2011

arrow_upward
0
arrow_downward

Какъв тип данни е AreaID?

Ако това е текстово поле, можете да направите нещо подобно

WHERE ( 
         AreaID LIKE '53,%'     -- Covers: multi number seq w/ 53 at beginning  
      OR AreaID LIKE '% 53,%'   -- Covers: multi number seq w/ 53 in middle
      OR AreaID LIKE '% 53'     -- Covers: multi number seq w/ 53 at end
      OR AreaID = '53'          -- Covers: single number seq w/ only 53
      )

Забележка: Не съм използвал SQL-Server от известно време, така че не съм сигурен за операторите. PostgreSQL има функция за регулярен израз, която би била по-добра при кондензирането на този оператор WHERE. Освен това не съм сигурен дали горният пример ще включва числа като 253 или 531; не би трябвало, но все пак трябва да проверите.

Освен това има куп функции, които итерират през масиви, така че съхраняването им като масив срещу текст може да е по-добро. И накрая, това може да е добър пример за използване на съхранена процедура, така че да можете да извикате вашата домашно създадена функция, вместо да претрупвате вашия SQL.

person vol7ron    schedule 06.02.2011

arrow_upward
0
arrow_downward

Използвайте функция за разделяне, за да конвертирате стойности, разделени със запетая, в редове.

CREATE TABLE Areas (AreaID int PRIMARY KEY);
CREATE TABLE Houses (HouseID int PRIMARY KEY, AreaIDList varchar(max));
GO

INSERT INTO Areas VALUES (84);
INSERT INTO Areas VALUES (24);
INSERT INTO Areas VALUES (66);
INSERT INTO Houses VALUES (1, '84,24,66');
INSERT INTO Houses VALUES (2, '24');
GO

CREATE FUNCTION dbo.Split (@values varchar(512)) RETURNS table
AS
RETURN
    WITH Items (Num, Start, [Stop]) AS (
      SELECT 1, 1, CHARINDEX(',', @values)
      UNION ALL
      SELECT Num + 1, [Stop] + 1, CHARINDEX(',', @values, [Stop] + 1)
      FROM Items
      WHERE [Stop] > 0
    )
    SELECT Num, SUBSTRING(@values, Start, 
        CASE WHEN [Stop] > 0 THEN [Stop] - Start ELSE LEN(@values) END) Value
    FROM Items;
GO

CREATE VIEW dbo.HouseAreas
AS
    SELECT h.HouseID, s.Num HouseAreaNum,
        CASE WHEN s.Value NOT LIKE '%[^0-9]%'
               THEN CAST(s.Value AS int)
            END AreaID
    FROM Houses h
    CROSS APPLY dbo.Split(h.AreaIDList) s
GO

SELECT DISTINCT h.HouseID, ha.AreaID
FROM Houses h
INNER JOIN HouseAreas ha ON ha.HouseID = h.HouseID
WHERE ha.AreaID = 24
person Anthony Faull    schedule 06.02.2011