Code :
DROP FUNCTION Get10ComPerDep; DROP TABLE com; DROP TABLE dep; CREATE TABLE dep ( nomdep char(23) NOT NULL PRIMARY KEY, numdep char(2) NOT NULL ); CREATE TABLE com ( insee char(5) NOT NULL PRIMARY KEY, codpos char(5) NOT NULL, nomcom char(26) NOT NULL, nomdep char(23) NOT NULL REFERENCES dep(nomdep) ); CREATE INDEX ix_nomcom ON com (nomcom); CREATE INDEX ix_codpos ON com (codpos); go CREATE FUNCTION Get10ComPerDep() returns @ret TABLE ( nomdep char(23), codpos char(5), nomcom char(26), insee char(5) PRIMARY KEY ) AS begin declare @nomdep char(23); declare curdep cursor FOR SELECT nomdep FROM dep; open curdep; fetch next FROM curdep INTO @nomdep; while @@fetch_status = 0 begin INSERT INTO @ret (nomdep, codpos, nomcom, insee) SELECT top 10 @nomdep, codpos, nomcom, insee FROM com WHERE nomdep = @nomdep ORDER BY codpos; fetch next FROM curdep INTO @nomdep; end; close curdep; deallocate curdep; RETURN; end; go INSERT INTO dep (nomdep, numdep) SELECT DISTINCT departement, LEFT(codepos, 2) FROM insee ORDER BY 1; INSERT INTO com (insee, codpos, nomcom, nomdep) SELECT insee, codepos, commune, departement FROM insee ORDER BY 1; SELECT * FROM Get10ComPerDep() ORDER BY nomdep, nomcom;
|