ok, am gasit si o solutie mai aproape de "spiritul" sql
iata pasii:
1. am facut un camp de id care se autoincrementeaza
asa ar fi tabelul care l-am folosit eu pt test:
USE [Intera_Sql_Functions]
GO
/****** Object: Table [dbo].[eug_tst] Script Date: 09/24/2007 17:11:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eug_tst](
[id] [int] IDENTITY(1,1) NOT NULL,
[cond] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[rez] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_eug_tst] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
si el continea datele de test:
1 | 6a | 2 |
2 | 6b | NULL |
3 | 6b | NULL |
4 | 6a | 3 |
5 | 6b | NULL |
6 | 6b | NULL |
7 | 6b | NULL |
8 | 6a | f |
9 | 6b | NULL |
10 | 6b | NULL |
NULL | NULL | NULL |
2. am facut un view care sa continea doar inregistrarile ce contineau 6A:
USE [Intera_Sql_Functions]
GO
/****** Object: View [dbo].[eug_tmp] Script Date: 09/24/2007 17:13:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[eug_tmp]
AS
SELECT TOP (100) PERCENT id, cond, rez
FROM dbo.eug_tst
WHERE (cond = N'6a')
ORDER BY id
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[36] 4[25] 2[12] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "eug_tst"
Begin Extent =
Top = 7
Left = 48
Bottom = 125
Right = 222
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1176
Output = 720
Append = 1400
NewValue = 1170
SortType = 1356
SortOrder = 1416
GroupBy = 1356
Filter = 1356
Or = 1350
Or = 1350
Or = 1350
End
End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'eug_tmp'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'eug_tmp'
3. am facut un view care continea id-urile recordurilor care incep cu 6A corespunzatoare fiecarui record ce incepe cu 6B
USE [Intera_Sql_Functions]
GO
/****** Object: View [dbo].[eug_tmp_2] Script Date: 09/24/2007 17:16:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[eug_tmp_2]
AS
SELECT TOP (100) PERCENT dbo.eug_tst.id, dbo.eug_tst.cond, dbo.eug_tst.rez, MAX(dbo.eug_tmp.id) AS Expr1
FROM dbo.eug_tst INNER JOIN
dbo.eug_tmp ON dbo.eug_tst.id > dbo.eug_tmp.id
GROUP BY dbo.eug_tst.id, dbo.eug_tst.cond, dbo.eug_tst.rez
HAVING (dbo.eug_tst.cond = N'6b')
ORDER BY dbo.eug_tst.id
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[41] 4[20] 2[11] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "eug_tst"
Begin Extent =
Top = 7
Left = 48
Bottom = 125
Right = 222
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "eug_tmp"
Begin Extent =
Top = 7
Left = 270
Bottom = 125
Right = 444
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
End
End
Begin CriteriaPane =
Begin ColumnWidths = 12
Column = 1440
Alias = 900
Table = 1176
Output = 720
Append = 1400
NewValue = 1170
SortType = 1356
SortOrder = 1416
GroupBy = 1356
Filter = 1632
Or = 1350
Or = 1350
Or = 1350
End
End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'eug_tmp_2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'eug_tmp_2'
4. la sfarsit am facut un query care sa scoata campul care consideram eu necesar:
SELECT dbo.eug_tmp_2.id, dbo.eug_tmp_2.cond, dbo.eug_tmp_2.rez, dbo.eug_tst.rez AS Expr1
FROM dbo.eug_tmp_2 INNER JOIN
dbo.eug_tst ON dbo.eug_tmp_2.Expr1 = dbo.eug_tst.id
--------------------------------------------------------------------------------------------------------------------------
eu am incercat sa simulez cat de cat problema pe care ziceai tu ca o ai, nu stiu exact in ce masura am intuit detaliile, plus de asta stiu ca exprimarea mea mai lasa de dorit asa ca daca sunt ceva neclaritati anuta si vedem..
bafta.