- Re: update ciudat

Welcome to Sign in | Help

Home

Blogs

External Feeds

» Romanian Section » Intrebari » Re: update ciudat

Re: update ciudat

09-24-2007, 5:20 PM

Gherry
Joined on 10-12-2006
sysadmin

Re: update ciudat

Reply Quote

ok, am gasit si o solutie mai aproape de "spiritul" sql Stick out tongue

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. Smile

Report abuse

View Complete Thread

Powered by Community Server (Commercial Edition), by Telligent Systems